This article is part of an Essential Guide, our editor-selected collection of our best articles, videos and other content on this topic. Explore more in this guide:
3. - Accounting and finance Excel tips: Read more in this section
- Use the MATCH function to find the next largest value
- How to build a year-over-year report using a pivot table
- Rank the top five customers in an Excel invoice register
Explore other sections in this guide:
- 1. - Problems with using Excel for finance
- 2. - Moving from Excel to financial management applications
Most people are familiar with the exact match version of =VLOOKUP(A2,Table,2,FALSE) that is used to find a value, such as the customer name associated with a particular Customer ID. But you might wonder, what's the purpose of the VLOOKUP where the fourth argument is TRUE?
Say you have a bonus program that pays a bonus for sales over $500, $1000, $2500 or $5000. The lookup table might appear as shown here:
Someone records a sale of $1235. What is their bonus? According to the table, it's $12. But a regular VLOOKUP would return #N/A since $1235 is not found in the first column of the table.
By specifying TRUE as the last argument in VLOOKUP, Excel will look through the table trying to find an exact match for $1235. If a match is not found, Excel returns the bonus from the next smallest value in the table.
The formula in D2 in the following figure is =VLOOKUP(C2,$F$2:$G$6,2,TRUE).
Many people believe the lookup table must be sorted by the first column. While that is a myth for VLOOKUP formulas ending in FALSE, it is true for the range match version of VLOOKUP. If your table is not sorted from smallest to largest, the VLOOKUP may not find the correct answer.
You can still get an #N/A error from this type of VLOOKUP if you are looking up a value smaller than the smallest value in the table. That's why the table includes a first row with a value of 0. If your data set could potentially include negative values, you should start the table with -9999 instead of 0. (Perhaps you would even include a negative bonus in case someone returned a $1235 sale.)
Most VLOOKUP functions in use today end in FALSE. The TRUE at the end of this VLOOKUP serves to alert anyone looking at your worksheet that this formula is different than most. However, since the TRUE argument is the default argument, it is possible to shorten the formula to: =VLOOKUP(C2,$F$2:$G$6,2).
Bill Jelen, AKA "MrExcel," has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.