Essential Guide

Ultimate Excel finance guide

A comprehensive collection of articles, videos and more, hand-picked by our editors
Q

Using VLOOKUP in a range match

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

FROM THE ESSENTIAL GUIDE:

Ultimate Excel finance guide

+ Show More

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.

This was last published in September 2013

PRO+

Content

Find more PRO+ content and other member only offers, here.

Essential Guide

Ultimate Excel finance guide

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

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

Please create a username to comment.

Is it possible to look up a number that falls between two numbers.

E.G. your table as
Range Start   -   Range End         Result
         1                     12                    ABC
        13                    28    DEF
        29                   480 GHI
        481                 496   JKL

Searching for the Result for numbers 1, 5, 15, 22, 29, 230, 289, 488 and 496
Cancel

-ADS BY GOOGLE

SearchManufacturingERP

SearchSAP

SearchOracle

SearchBusinessAnalytics

SearchContentManagement

SearchDataManagement

SearchCRM

Close