VLOOKUP with numbers and text

To use the VLOOKUP function to look up information in a table where the first column contains numbers that are actually 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)

where id (H4) and planets (B5:B13) are named ranges. With the number 5 in cell H2, the formula in H5 returns "Jupiter".

Note: This example is a workaround to the problem of mismatched numbers and text, which causes VLOOKUP to return an #N/A error. If there is no mismatch, the workaround is not necessary and you can use a normal VLOOKUP formula.

Explanation

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:

Example of VLOOKUP #N/A error caused by numbers and text mismatch

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:

Revised VLOOKUP formula to fix 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:

=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 above. If that formula also fails, VLOOKUP will return an #N/A error as always.