Problem solve Get help with specific problems with your technologies, process and projects.

Using VLOOKUP in a range match

Microsoft Excel consultant Bill Jelen, AKA 'MrExcel,' walks readers through an exercise on using VLOOKUP.

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:

Figure 1

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).

Figure 2

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 25 years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.

Dig Deeper on ERP accounting software