Why Your VLOOKUP Is Not Working

vlookup1The two most common frustrations with the VLOOKUP formula is either the lookup spits out the wrong value or you get the dreaded #VALUE. Even seasoned Excel experts can get stumped by this. However, 9.5 times out of 10 the reason your VLOOKUP formula is not working is due to one simple word: FALSE. Let me explain.

Let’s say you have the set of data below with the VLOOKUP formula in cell E2 to look up Mary.

vlookup2

Two things should stick out to you. First, the formula (as most people are concerned) is 100% correct. Second, the result of the lookup should be 34, not 68.

VLOOKUP actually works in a different way than most people think. The formula will find the closest (which very often does not seem that way) match to your lookup criteria. This can produce some whacky results. For instance, the above example picked Billy as the closest match to Mary. One fix for this is to sort your data from A to Z. This will usually fix your problem. If you are too lazy to sort or if that is not an option, all you have to do is modify your formula as follows:

=VLOOKUP(D2,A1:B7,2,FALSE)

When you input the FALSE into the formula, you are telling Excel that you want the VLOOKUP to only lookup values that are exactly the same as your criteria.

Use FALSE on all of your VLOOKUP formulas from now on. I promise it will alleviate you from needless stress and incorrect data. Share this knowledge with your follow co-workers as the greatest Christmas present one can give.