Excel Series Not Continuing Using vlookup

[MSkog23]

I am using vlookup to pull matching information from one spreadsheet to the other. I can complete the formula and it is working, but I need it to continue the series for 2000 rows. When I try to copy the formula it does not continue the series, which just ends up leaving errors. I have looked at all the most common solutions and nothing has fixed it.

[#2]

@MSkog23 

=IFERROR(INDEX($G$2:$G$27,SMALL(IF($F$2:$F$27=$B$1,ROW($F$1:$F$26)),ROW(1:1))),"")

You can try this formula with the data layout of the example. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. The ranges of the formula can be adapted as required. In cell B1 in the attached file you can enter the criteria and the formula dynamically updates the result.

[#3]

@OliverScheurich 

I'm looking at specifically using the vlookup formula. What is happening is when I try to continue the series, it just copies the formula exactly. Example: =VLOOKUP(A2,'[sheetdataison.xlsx]Sheet1'!$A$2:$J$2,10,FALSE) gets me the data I need, but when I try to continue it in a series, it keeps "$A$2:$J$2" instead of continuing it, like $A$3:$J$3, $A$4:$J$4, etc...

[#4]

@MSkog23 

=VLOOKUP(A2,Tabelle2!$A2:$J2,10,FALSE)

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.