VLOOKUP to Find the Closest Match - Last Argument TRUE
21

How to Use VLOOKUP to Find the Closest Match – Last Argument is TRUE

Bottom line: Learn how to use the VLOOKUP function to find the closest match by setting the last argument to TRUE.

Skill level: Intermediate

Do you have a common activity you do when you stay home sick from work or school?  For me, it always included watching an episode of The Price is Right with Bob Barker.

VLOOKUP Closest Match and The Price is Right

If you have never seen it, The Price is Right is a game show in the U.S. where contestants have to guess the prices of various household items.  For the Showcase game, the contestant must guess a price that is equal to or less than the actual retail price of the items to win the game.

If the contestant could use a vlookup formula to make their guess, they would win every time!  The contestant would have to set the last argument in the vlookup to TRUE for this to work.

When you start using vlookup you typically set the last arguement [range lookup] to FALSE.  This tells vlookup to find an exact match for the text or number you are looking for.  Checkout my article on VLOOKUP Explained at Starbucks for more info.

Closest Match with VLOOKUP (TRUE)

Setting the last argument to TRUE tells VLOOKUP to find the closest match to the text or number you are looking for.  However, there is a caveat to this “closest match”…

The VLOOKUP starts at the top of the range you specify and looks down (vertically) in each cell to find the value you are looking for (lookup value).  It stops searching when it finds a value that is greater than or equal to the lookup value.

How VLOOKUP with Closest Match Works

In the screencast above the vlookup formula is looking for the number 14 in the list.  Since 14 is not in the list, it returns 10.  But why???

Well, there are three possible outcomes for vlookup when the last argument is TRUE.  The results may seem confusing at first, but they can actually be quite useful.

You can download the file to follow along.

VLOOKUP Closest Match Example.xlsx (13.2 KB)

Outcome #1: VLOOKUP Finds Exact Match

If vlookup finds a value that is equal to the lookup value then it returns a result from that same row.

VLOOKUP CLosest Match (TRUE) Exact Match Example

This is essentially the same as a normal vlookup where you set the last argument to FALSE.  The function finds an exact match and returns that row as a result.

Outcome #2: VLOOKUP Finds Closest Match

If vlookup finds a value that is greater than the lookup value, then it returns a result from the previous row.

VLOOKUP CLosest Match (TRUE) Greater Value Example

This means that the “closest match” is going to be less than or equal to the lookup value.

It’s also important to note that if the lookup value is greater than the last cell in the lookup range, then the result will be the value in the last cell.

For example, if the lookup value was 30 in the example above, vlookup would return cell B7 (25) as the result.  Since vlookup has no more values to look at, it stops at the last cell.

Outcome #3: VLOOKUP Returns an #N/A Error

If vlookup finds a value that is less than the first cell in the lookup range, then it returns an error.

VLOOKUP CLosest Match (TRUE) NA Error Example

We probably spend a lot of time figuring out why vlookup formulas are not working, and returning errors.  This is no exception when using vlookup to find the closest match.

If you are looking up numbers, then the first row in the lookup range needs to be the minimum amount you will potentially lookup.  I explain more about this in the example below.

Important: Sort the Lookup Range

Most of the time you will want the numbers in the first column of the lookup range sorted from smallest to largest (A-Z).

The “V” in VLOOKUP stands for vertical.  As I explained above, it starts at the top cell in the lookup range and works its way down.  As soon as it finds a cell greater than or equal to the lookup value, IT STOPS!

VLOOKUP CLosest Match (TRUE) Sort Smallest to Largest

So if you have values at the bottom of the list that are smaller, vlookup will never find them.

Example: Calculating Commissions with VLOOKUP

One of the best examples of this technique is calculating commissions.  Please see my article on calculating commissions with vlookup for a full explanation on this technique.

Calculate Commissions with VLOOKUP Closest Match

Click here to read the full article on calculating commissions

The Price is Right for VLOOKUP’s Closest Match

So this is why it would be great for The Price is Right contestants to have a vlookup at their disposal.  If they try to guess a price that is too high, vlookup would return the closest match that is equal to the actual retail price.  I know it’s a stretch, but think about it next time your writing a vlookup formula. 🙂

The host of the show, Bob Barker, always used to have the same closing statement at the end of the show about getting your pets neutered.  I think he had something against puppies and kittens… j/k

In closing this post, I will remind you to always sort the first column in your lookup range when finding the closest match with vlookup.

Please leave a comment below with any questions.  Thanks!

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 21 comments
Jessica - August 30, 2017

Hi Jon,

I’m trying to do a vlookup where I’m using an address say 115 King Street South as my lookup_value, but in the table_array the address may be more like 115 KING ST S. I want it to pull the associated available space (5,236 SF). I think the vlookup is missing the address because it is not exactly the same.

Is there a solution for this?

Thank you,

Jessica

Reply
Deborah Arline - April 4, 2017

Jon,

I feel like I’m missing something here. Where “Outcome #1: VLOOKUP Finds Exact Match” says:

“This is essentially the same as a normal vlookup where you set the last argument to FALSE. The function finds an exact match and returns that row as a result.”

The example has code with last argument value “True” and not “False”. Is the example right? If it is, perhaps I need to spend more time ingesting it.

Great article!
Love the “Closest Match” moving illustration!!!
– Deborah

Reply
    Jon Acampora - April 10, 2017

    Hi Deborah,
    In that first outcome, if the lookup value exists in the table_array then the Vlookup will return the row of the matching value. This is the same behavior as if the last argument were set to FALSE. In that sentence I was just trying to say that it is the same behavior as if the last argument were set to FALSE. The argument is still set to TRUE for this example. Sorry if that was confusing.

    Reply
Indira Galan - March 10, 2017

Hi John,

How would I go about using the excel lookup for commission when the tiers are I different groups. For example let’s say there are 2 groups (so 2 tables) you fall under depending on your job title. So I have 4 categories and 3 of them are in Group A and one is in group B. How would I make the calculations so they know which table to pull from?

Reply
    Jon Acampora - March 20, 2017

    Hi Indira,
    Great question! You can use the IF or CHOOSE function to first determine what group they are in, then use the vlookup based on the group. The basic outline of the formula would look like the following.

    =IF(A2=”Group A”, Vlookup(),Vlookup())

    If there are more than two groups then you can use the CHOOSE function. I cover this in more detail in my Ultimate Lookup Formulas Course.

    I hope that helps. 🙂

    Reply
Phil - February 28, 2017

Dead simple explanation, thank you!

Of course, with me, this results in a new question: will this work with times, rather duration, as well? Reason I ask is that I’m part of a running club and one of the runners suggested we should guess all our groups participants for the next half marathon.

I’d love to be able to create a sheet where the runners can fill in their estimate for all their fellow runners and have the sheet show who was the closest to the actual times they ran.

Reply
Sunil Munshi - February 15, 2017

Dear Jon,

I still fail to understand that why would we at all be looking for a closest match, when the cell is filled with an exact match.
Can you please throw some light.

Thanks in advance
Sunil

Reply
John Weiss - December 9, 2016

here’s a way to find the the next number in sequence that’s equal to or greater than the search-value. Assume 4 is the search-value, and $A$1:$A$10 is the search-range. Should be entered as an array formula (CTRL-SHIFT-ENTER):

=MIN(IF($A$1:$A$10 >= 4, $A$1:$A$10, “”))

Reply
    Jon Acampora - December 10, 2016

    Thanks for the suggestion John! We can also use the MATCH function and set the [match_type] parameter to -1 to find a value greater than the search value. This avoids the need for an array formula. So many ways to accomplish a task in Excel. I guess that’s what makes it fun. 🙂

    Reply
Chip Burkert - August 31, 2016

I am interested in finding literally the closest number. In your examples above when you search for 14, it returns 10 which is next lower number. But 14 is closer to 15 than it is to 10 so how can I get Excel to return “15” when I go into the table with a lookup value of 14? (Or is that what the “False” parameter does?)

Thanks,
Chip

Reply
Maria - June 28, 2016

You make excel functions seem simple!
Thank you.

Reply
Anas Javed - May 13, 2016

Wonderfully explained.

Reply
The Dangers of Binning in Excel | My Spreadsheet Lab - December 12, 2015

[…] How to Use VLOOKUP to Find the Closest Match (TRUE)  post by Jon Acampora […]

Reply
mano - July 12, 2015

Dear Jon,
Hi
thankfully for you because learn excel tips and vba.

Reply
Wanda - April 11, 2015

You are so succinct in how you explained the closest match! You have explained it better than other websites I have visited. You have the gift!

Reply
Damith Jayaruwan - April 10, 2015

Actually, all these vlookup tutorials were really helpful to me. I am working in a manufacturing company & advanced excel knowledge is far more important to work with complex reports to me and therefore, I wanted to find a source to learn excel more.When i was searching on net i could find your free excel tutorials and after that i registered with your site now i know considerable things about excel vlookup,pivot table, and some more.And also,I must be grateful to you for your valuable support for worldwide people who really have a thirst to learn excel.Finally, I wish you all the best for your campaign and I need to refer your tutorials further to sharpen my excel knowledge.

Thank you,
Damith Jayaruwan.

Reply
    Jon Acampora - April 13, 2015

    Thank you for the kind words Damith! I’m happy to hear that you are learning Excel and enjoying the tutorials. Thanks for subscribing to the newsletter as well. Have a great day! 🙂

    Reply

Leave a Reply:

FREE Live Webinar 

The 5 Secrets to Understanding Pivot Tables
x

Grab My Free Cheat Sheet!

VLOOKUP Essentials Guide​

Vlookup Essentials Guide 317x407
Plus: Free Video Training Series on the Lookup Formulas!
x