Microsoft Excel - VLOOKUP - VLOOKUP with numbers and text

A common problem with VLOOKUP is a mismatch between numbers and text.

Either the first column in the table contains lookup values that are numbers stored as text, or the table contains numbers, but the lookup value itself is a number stored as text.


Try

=VLOOKUP(val&"",table,col,0)

NOTE: Concatenating an empty string to the lookup value converts the lookup value to text.


Try

=VLOOKUP(value(val),table,col,0)

Try

=VLOOKUP(TEXT(val,"@",table,col,0)

Try

=VLOOKUP(TEXT(val,"#"),table,col,0)