Replace OFFSET with INDEX (EXCEL)

Question

I'm using a formula with OFFSET, I would like to replace OFFSET by INDEX, so I can get the data in another file. can anybody help me?

=OFFSET(DATA!$C$1,MATCH(C2,DATA!$C$2:$C$3500,0),8,COUNTIF(DATA!$C$2:$C$3500,C2))

This formula I use as a list, within the data validation.

Tks!!!

Answer 1

Use:

=INDEX(DATA!$K$2:$K$3500,MATCH(C2,DATA!$C$2:$C$3500,0)):INDEX(DATA!$K$2:$K$3500,MATCH(C2,DATA!$C$2:$C$3500,0)+COUNTIF(DATA!$C$2:$C$3500,C2))

Or with FILTER():

=FiLTER(DATA!$K$2:$K$3500,DATA!$C$2:$C$3500=C2)