VLOOKUP On Dynamic Range Returns Only #N/A

[goss]

Hi,

Using Excel 2016.

I created a Dynamic Range of items on Test!
lstItems=Test!A$2:INDEX(Test!A:A,COUNTA(Test!A:A))

On another sheet I have a VLOOKUP formula
=VLOOKUP(B2,lstItems,1,FALSE)

The formula only returns #N/A on 11,036 attempts.

I can visually see that the item exists on both sheets.

What am i doing wrong?

thanks

 

[goss]

Added absolutes to the dynamic range. Solved.
=Test!A$2:INDEX(Test!$A:$A,COUNTA(Test!$A:$A))

 

[Yongle]

Lazy perhaps , but the value in A1 is never going to find a match. Why not use the whole column, as the RefersTo for your named range, like this...

=test!$A:$A

It may not be good enough for some jobs, but is fine for this task