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 first published in September 2013

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

SearchManufacturingERP

SearchSAP

SearchOracle

SearchBusinessAnalytics

SearchContentManagement

SearchDataManagement

SearchCRM

Close