Replace OFFSET with INDEX (EXCEL)-CodePudding

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!!!

CodePudding user response：

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)