Every VLOOKUP Ends in False
April 05, 2022 - by Bill Jelen
Problem: My VLOOKUPs aren’t working. It is returning other values from the table.
Strategy: You have to end your VLOOKUPs with FALSE as the fourth argument. If you don’t put FALSE at the end of your VLOOKUP, then you are using a completely different function. There are people who leave the FALSE off the VLOOKUP. If you don’t specify FALSE, then you are letting Excel assume that you want TRUE as the range_lookup argument. You are asking for trouble. You will hate VLOOKUP when you e-mail an incorrect document to the entire department. Don’t ever write a VLOOKUP that does not end in FALSE.
The only exceptions:
- Some people put a zero instead of FALSE. That’s fine. It saves you 4 characters of typing, but it still runs the FALSE version of
VLOOKUP. - Commission accountants have permission to use the approximate version of
VLOOKUP, but only 1% of the time. They must specifically be trying to eliminate a bunch of nested IF statements as shown all the way back in Figure 389. - Scientists. I get it. They do range lookups all the time. If you are a scientist reading this book, send it back to me and I will send you Gerry Verschuuren’s Excel for Scientists book.
- Very clever Excel tricksters will utilize a loop hole in the range_lookup version of
VLOOKUPto return the last non-blank value in a row or column.
Everyone else should be using the ,FALSE version of VLOOKUP every time.
This article is an excerpt from Power Excel With MrExcel
Title photo by Fahrul Azmi on Unsplash
