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. 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: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
