VLOOKUP with numbers and text
Generic formula
=VLOOKUP(val&"",table,col,0)
Summary
To use the VLOOKUP function to retrieve information from a table where the key values are numbers stored as text, you can use a formula that concatenates an empty string (“”) to the numeric lookup value, coercing it to text. In the example shown, the formula in H3 is:
=VLOOKUP(id&"",planets,2,0) // returns "Earth"
where id (H2) and planets (B3:B11) are named ranges.
Note: The example is a workaround to the problem of mismatched numbers and text, which cause a #N/A error. If there is no mismatch, the workaround is not necessary and you can use a normal VLOOKUP formula.https://imasdk.googleapis.com/js/core/bridge3.470.1_en.html#goog_62510261512.6MExplanation
A common VLOOKUP error is a mismatch between numbers and text. Most typically, the lookup column in the table contains numeric values that look like numbers, but are in fact numbers stored as text. When a genuine number is passed into VLOOKUP as the first argument, the formula returns a #N/A error, even though there appears to be a match. The screen below shows an example of this problem:
The numbers in column B are actually text, so the numeric lookup value, 3, fails, even though it seems like VLOOKUP should match B5 and return “Earth”. You can enter a number as a text value by preceding the number with a single quote (‘).
The best solution is to make sure the lookup values in the table are indeed numbers. However, if you don’t have control over the table, you can modify the VLOOKUP formula to coerce the lookup value to match the type in the table. In the example shown, we coerce the numeric lookup value to text by concatenating an empty string:
=VLOOKUP(id,planets,2,0) // original =VLOOKUP(id&"",planets,2,0) // revised
And the revised formula takes care of the error:
You could also do the same thing with a longer formula that utilizes the TEXT function to convert the number to text:
=VLOOKUP(TEXT(id,"@"),planets,2,0)
Both numbers and text
If you can’t be certain when you’ll have numbers and when you’ll have text, you can cater to both options by wrapping VLOOKUP in the IFERROR function and using a formula that handles both cases:
=IFERROR(VLOOKUP(id,planets,3,0),VLOOKUP(id&"",planets,3,0))
Here, we first try a normal VLOOKUP formula that assumes both lookup value and the first column in the tables are numbers. If that throws an error, we try again with the revised formula. If that formula also fails, VLOOKUP will return an #N/A error as always.
Ref:
https://exceljet.net/formula/vlookup-with-numbers-and-text
https://www.extendoffice.com/documents/excel/3802-excel-vlookup-numbers-stored-as-text.html