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!
VLOOKUP Trouble
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.
=VLOOKUP(B8,$B$2:$D$5,2,FALSE)
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.
___________________
I always use the following format
=IF(ISNA(vlookup(field,database,col#,false))=TRUE,””,vlookup(field,database,col#,false))
No nasty #N/A ever appear.
A couple of other common errors I come across, are –
– Not using absolute references (or range names) for the LOOKUP_TABLE.
With the result that the formula will work for some of the cells, but it will show #N/A for many other cells !
I’ve seen users go crazy trying to figure out why the formula won’t work, because they *know* that the lookup_value *is* there in the lookup_table – they have used Ctrl+F to confirm that 🙂
– Not using FALSE or 0 (for exact match), when they *should* be using it.
I usually nest my VLOOKUPs with a MATCH due to the information being in a time series format. This is when absolute reference errors tend to occur for me. In the past when I’ve messed a formula up I have redone it until I get the desired results, but that doesn’t happen much anymore.
1000 thanks for this one, i was pretta desperate about it. converting all alues didn’t help or work, but adding an empty string solved it!