excel - VLOOKUP formula returns '#VALUE!' instead of the text value it should return

Question

I wrote this formula in an Excel cell:

=IF(VLOOKUP(A2, VS!$B2:$B98,1,FALSE ),A2,NA)

What I want to do is, if it finds the A2 value in VS table from B2 to B92 then the function will return and input the value in A2 (in VS table) to my current table. But instead of getting the A2 values, which are text values, I got #VALUE!.

How can I solve it?

Answer 1

Try the following:

=IFERROR(VLOOKUP(A2,VS!$B2:$B98,1,FALSE),"")

What this does is return the value if it finds A2, in the range B2:B98, it doesn't find A2, it returns "" (blank), instead of #VALUE.

Secondly if you're planning on extending this formula, you may want to make the table more 'strictly typed' by adding '$' before the numbers so the range doesn't shift:

=IFERROR(VLOOKUP(A2,VS!$B$2:$B$98,1,FALSE),"")

Lastly, try right click -> Format Cells... and format column A as 'Text' and column B (on sheet VS) as Text as well. Sometimes Excel's Autoformatting features mess with vlookup's results.