Using VLOOKUP in a range match

Bill Jelen

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

View the next item in this Essential Guide: Adding tick marks in Excel or view the full guide: Ultimate Excel finance guide

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: