You’re admired by your co-workers, thanks to your awesome Excel skills. Cupcakes magically appear on your desk, in thanks for your help with complex formulas. Your boss keeps stretching the budget, to accommodate the huge bonuses you get, in reward for your amazing talents. Well, that might not be the exact situation, but I’m sure your skills are appreciated!
Then, one day, it all goes horribly wrong. Your boss needs a report in 15 minutes, and you can’t get a seemingly simple VLOOKUP formula to work. You can see the target product numbers in the lookup table, but the formula result is #N/A, instead of the product price.
You don’t want to lose your Excel Expert badge over something this trivial, so how will you solve the problem?
Text or Number?
A common cause for this VLOOKUP error is that one of the values is a number, and the other is text. In this example, the lookup table codes in cell B2:B5 are stored as text values – they have a leading apostrophe. The lookup code, in cell B8, is entered as a number – no leading apostrophe.
So, it looks like cells B2 and B8 are equal, but Excel sees them as different values.
Fix the Text Values
The easiest solution to the the VLOOKUP problem in this example is to convert the text values to numbers, so the codes in the table match your lookup values.
Or, type an apostrophe in front of your lookup code in cell B8, so it’s a text value too.
Change the VLOOKUP Formula
If you can’t fix the data, you can convert the lookup value in the Excel VLOOKUP formula. Here is the original VLOOKUP formula, that returned an #N/A error.
If you add an empty string to the end of the value in cell B8, the lookup number will be converted to a text string. The revised formula is:
=VLOOKUP(B8 & “”,$B$2:$D$5,2,FALSE)
This formula will also work if cell B8 contains a text value – adding the empty string won’t change the value.
More Excel VLOOKUP Troubleshooting
If this VLOOKUP formula fix doesn’t solve the problem, there are more Excel VLOOKUP troubleshooting tips on the Contextures website.
Have you run into this problem? How did you fix it?
Watch the Excel VLOOKUP Troubleshooting Video
To see the steps for fixing the Excel VLOOKUP problem, you can watch this short Excel video tutorial.
Or watch on YouTube: Excel VLOOKUP Formula Troubleshooting