In this example, the goal is to configure VLOOKUP to perform a lookup in a table where the first column contains numbers entered as text, and the lookup value is a true number. This mismatch between numbers and text will cause VLOOKUP to return an #N/A error.
Typically, the lookup column in the table contains values that look like numbers, but are in fact numbers entered as text. When a true number is passed into VLOOKUP as the lookup_value, VLOOKUP returns #N/A, even though there appears to be a match. The screen below shows an example of this problem:
The cause of the #N/A errors is that the numbers in column B are actually text, so a lookup value of 5 fails, even though it seems like VLOOKUP should match cell B9 and return information about Jupiter. The formulas in H5:H7 are as follows:
=VLOOKUP(id,planets,2,0) // name =VLOOKUP(id,planets,3,0) // diameter =VLOOKUP(id,planets,4,0) // satellites
All of these formulas return #N/A since they are all affected by the mismatch. One quick solution to the problem is to enter the lookup value in id (H4) as text instead of a number. You can do this by prefacing the number with a single quote ('). VLOOKUP will then correctly find the table and perform the lookup. A better solution is to make sure the lookup values in the table are indeed numbers. Once you have converted the first column to numeric values, the standard VLOOKUP formulas above will work.
However, if you don't have control over the table, you can modify the VLOOKUP formula itself to change the numeric lookup value to text in order to match the lookup table. You can do this by concatenating an empty string ("") to the lookup value inside VLOOKUP. The formulas below show what this looks like. The first formula is a standard VLOOKUP, the second formula shows the modified version:
=VLOOKUP(id,planets,2,0) // original =VLOOKUP(id&"",planets,2,0) // modified
The modified version will take care of the error:
If you aren't sure if the first column in the lookup table is text or numbers, use the ISTEXT function or the ISNUMBER function to test the values.
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 by wrapping VLOOKUP in the IFERROR function and using a formula that handles both cases:
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 above. If that formula also fails, VLOOKUP will return an #N/A error as always.