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 Fig 403.
- 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 VLOOKUP to return the last non-blank value in a row or column.
Everyone else should be using the ,FALSE version of VLOOKUP every time.