How to Use Excel VLOOKUP to Find the Closest Match

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, we want to find a unique value in a large set of data, or an approximate value of a certain data. But when the data are huge, it is not a wise way to search for them manually. In this article, you will learn how to implement Excel VLOOKUP to find the closest match with the five most efficient examples.


VLOOKUP Function in Excel: Syntax

Microsoft Excel’s one of the most powerful, flexible, and extremely useful functions to search and retrieve values – either exactly matched values or the closest matched values – by looking up a corresponding value is the VLOOKUP function. For instance, you may want to find the name of a student who got 100 marks on the math test, but no one has achieved that mark. So now you want to find out who got the closest of 100 in the math exam. With the help of the VLOOKUP function, you can find both of them easily.

In this article, we will discuss how to find the closest match in a dataset in Excel with the VLOOKUP function with five of the most convenient examples.

But before that, let’s figure out how VLOOKUP works, what are the arguments of the function, and the definition of the arguments.

Generic formula:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Here,

Arguments Definition
lookup_value The value you are trying to match
table_array The data range that you want to search your value
col_index_num Corresponding column of the lookup_value
range_lookup This is a Boolean value: TRUE or FALSE.
FALSE (or 0) means exact match and TRUE (or 1) means approximate match.

Key Points You Must Keep in Mind

Before diving into the calculation part, there are certain things that you should keep in mind while working with the VLOOKUP function. Otherwise, you will get errors.

  • For the VLOOKUP function to work accurately, you have to sort the data in the table in ascending order.
  • As the range of the data table array to search for the value is fixed, so don’t forget to put the dollar ($) sign in front of the cell reference number of the array table.
  • When working with array values, don’t forget to press Ctrl + Shift + Enter on your keyboard while extracting results. Pressing only Enter doesn’t work while working with array values.

In this section, we will discuss the five most productive examples to find the closest match in Excel with the VLOOKUP function. We will show how to utilize the VLOOKUP function to find a partial match in text, find the closest value of a predefined value, find the commission rate of sales personnel, find the best candidate for a job and find the upcoming event date.

1. Using VLOOKUP to Find the Partial Match in Text in Excel

Suppose you want to extract information about a person only by his first name from a large dataset. VLOOKUP function in Excel will give you the answer based on that partial text match.

Let’s find out how to do that with an example.

Consider the following data table, where we wanted to find out the population of the “South Region”, but we didn’t want to write the long name in our search bar. So, all we did was, write the region name and in the next cell, run the VLOOKUP formula.

According to the generic formula of the VLOOKUP function, our formula was,

=VLOOKUP(B6, $B$5:$C$8, 2, TRUE)

Explanations:

Look for -> B6 = South Region Population

In -> $B$5:$C$8 = Range of the Array Data Table

2 = To return the value in the same row from the second column of the table array

TRUE = To get the approximate match

vlookup closest match to text

But it didn’t give us the population of the “South Region”. What was the problem?

The problem is, that VLOOKUP performs range-wise, meaning it scans down the first column and returns the row where the value is greater or equal to the row and less than the value in the next row.

In regard to the text values, it is kind of harder to execute the lookup operation in Excel. But theoretically, the text string “North Region Population” is equal to “South Region Population” so the value from the “North Region Population” row is returned.

After such a long run, this is a complete disaster, right? Hold on, here comes the rescue part.

To get an accurate result based on a partial-string match, use a character to represent another character. For instance, we used an Asterisk (*) and concatenated our lookup value with it using the Ampersand (&) symbol.

So now our formula looks like this,

=VLOOKUP(B11&"*", $B$5:$C$8, 2, TRUE)

vlookup closest match to text

And it works completely fine. As shown in the picture above, by implementing the formula, VLOOKUP gave us the Population of the whole “South Region” in the result cell C11.

Read More: How to Use VLOOKUP to Find Partial Text from a Single Cell


2. Using Excel VLOOKUP to Find the Closest Match of the Lookup Value

What if you want to get a specific number but the number isn’t available in that dataset? So now you want to find the closest match possible to the number. Are you going to search for that approximate value manually in that huge dataset? No, right?

In this section, you will learn how to find the closest match of a certain value in a large dataset using VLOOKUP in Excel.

To describe the method more clearly, let’s learn how to do that with an example.

Consider the following data table, where we wanted to find out the closest match of our lookup value, 24.

So we wrote the formula as,

=VLOOKUP(F5, $C$5:$C$9, 1, TRUE)

Explanations:

Look for -> F5 = 24

In -> $C$5:$C$9 = Range of the Array Data Table

1 = To return the value from the corresponding column

TRUE = To get the approximate match

vlookup closest match to value

It gives us the result of 20, not 25; even though 24 is the closest to 25. And why is that?

Well, when we ask Excel to perform VLOOKUP, it starts performing a range lookup, meaning it scans down the first column and returns the row where the value is greater or equal to the row and less than the value in the next row.

So, first, it started scanning what is the highest less than the value of 24? When it reaches the value 25, it stops the execution and returns the previous row, 20, as the result.


3. VLOOKUP to Find the Commission Rate (Looking for the Closest Sales Value)

Suppose you want to find the commission rate of all the salespeople, how are you going to do that in a massive dataset?

Let us make you describe that with a simple example.

Consider the following dataset where the commission will be distributed based on the Sales, which will be calculated using the right-side table.

dataset to vlookup closest match for commission

For example, if a salesperson’s Sale Range is around 5000, then the Commission is 0%, if the Sale Range is around 20000, then the Commission is 7% and so on. So, to get the Commission Rate, you need to find the closest match of the Sale Range and just lower the Sales value.

vlookup closest match for commission rate

As an example, for the 16427 Sales value (cell C5), the Commission would be 5%, as it is below the 10000 Sale Range (cell F6). As a result, by calculating it with the Commission value from the right table (cell G6), the Commission Rate becomes 0.05 (cell D5).

And to calculate that result easily, we used the VLOOKUP function in Excel.

The formula to calculate the commission rate,

=VLOOKUP(C5,$F$5:$G$9,2, TRUE)

Explanations:

Look for -> C5 = 16427 Sales value of the salesperson, Cook

In -> $F$5:$G$9 = Range of the Array Data Table

2 = To return the value in the same row from the second column of the table array

TRUE = To get the approximate match

It calculates the commission rate of the salesperson named Cook and displays the result in the D5 cell.

dragging row to vlookup closest match for commission rate

Now just drag the row down using Fill Handle to apply the formula to the rest of the rows.


4. Selecting the Candidate with the Closest Experience Using Excel VLOOKUP

Excel’s LOOKUP functions do wonders in finding the right person for any job. Even though Excel uses a combination of formulas, it completes the task perfectly.

Let us consider the following table to find out how to do that.

Here in the sample data, we wanted to find the employee who has the work experience closest to the required value. We required 6 years of work experience in our case.

Now select an empty cell where you want the employee name to appear and in that cell write the following formula,

=INDEX($B$5:$B$9,MATCH(MIN(ABS(E5-C5:C9)),ABS(E5-$C$5:$C$9),0))

vlookup closest match for best candidate

It will give you the employee name to best fits the job.

Formula Explanations:

Let’s break down the formula to understand deeply how we found out the name Alex.

E5-$C$5:$C$9 -> Required Experience (6 years) – Year of Experience of Each Employee.

Output: 4.4, -1, 5.1, -4, 0.8

It subtracts the year of experience of each employee from the required experience and gives us multiple results because the Required Experience (cell E5) runs through the whole array table, calculates with every member (Cook, Root, Clark, Jimmy, Alex), and produces the results for all of them.

ABS(E5-$C$5:$C$9) -> The ABS function removes the minus sign (-) from a negative number, making it positive. The ABS function has no effect on 0 (zero) or positive numbers

Output: 4.4, 1, 5.1, 4, 0.8

MIN(ABS(E5-C5:C9)) -> The minimum value of the produced absolute (ABS) values.

Output: 0.8

MATCH(MIN(ABS(E5-C5:C9)),ABS(E5-$C$5:$C$9),0) -> Finds the position of the 0.8 (first argument: MIN(ABS(E5-C5:C9))) in the array constant (second argument: ABS(E5-$C$5:$C$9)). In this example, we wanted the MATCH function to return an exact match so we set the third argument to 0 (or FALSE).

Output: 5

INDEX($B$5:$B$9,MATCH(MIN(ABS(E5-C5:C9)),ABS(E5-$C$5:$C$9),0)) -> The INDEX function takes two arguments to return a specific value in a one-dimensional range. Here, the range $B$5:$B$9 is the first argument, and the result that we had from the calculation in the previous section, position 5, is the second argument. That means we are searching the value located in position 5 in the $B$5:$B$9 range.


5. Finding the Next Event Date with VLOOKUP in Excel

Last but not least, due to our busy schedule, sometimes we forget about the date of important events. In that case, the VLOOKUP function is to the rescue.

Below is the sample data where we wanted to know the upcoming birthday among our friends.

So what we did was, simply implement an Excel lookup formula to find out whose birthday it is next and when is that.

vlookup closest match to find event name

So the formula to know the person’s name or the upcoming event’s name is,

=INDEX($B$5:$B$9,MATCH(F5,$C$5:$C$9,1)+1)

vlookup closest match to find next event date

And the formula to know the date of the upcoming event is,

=INDEX($C$5:$C$9,MATCH(F5,$C$5:$C$9,1)+1)

Formula Explanations:

Let’s break down the formula to understand deeply how we found out the name Alex and the date of his birthday.

MATCH(F5,$C$5:$C$9,1) -> Finds the position of the lookup value (cell F5->Thursday, November 11, 2021) in the array constant ($C$5:$C$9 -> the list of the dates). In this example, we didn’t want an exact match, we wanted the MATCH function to return an approximate match, so we set the third argument to 1 (or TRUE).

Output: 4

INDEX($B$5:$B$9,MATCH(F5,$C$5:$C$9,1)+1) -> Takes two arguments to return a specific value in a one-dimensional range. Here, the range to $B$5:$B$9 is first the argument and the result that we had from the calculation in the previous section (MATCH(F5,$C$5:$C$9,1)), position 4, is the second argument. That means we are searching the value located in position 4 in the $B$5:$B$9 range.

Output: Alex/(The event’s name)

And,

INDEX($C$5:$C$9,MATCH(F5,$C$5:$C$9,1)+1) -> Takes two arguments to return a specific value in a one-dimensional range. Here, the range to $C$5:$C$9 is first the argument and the result that we had from the calculation in the previous section (MATCH(F5,$C$5:$C$9,1)), position 4, is the second argument. That means we are searching the value located in position 4 in the $C$5:$C$9 range.

Output: Tuesday, December 7, 2021

To get the upcoming event date, we just added one to the cell position returned by the MATCH function, and it will give you the cell position of the next event date.

Read More: How to Vlookup Partial Match for First 5 Characters in Excel


Download Practice Workbook

You can download the free practice Excel template from here and practice on your own.


Conclusion

This article explained in detail how to find the closest match in Excel using the VLOOKUP function in various aspects of our lives. I hope this article has been very beneficial to you. Feel free to ask any questions you have regarding the topic.


Related Articles


<< Go Back to VLOOKUP Partial Match | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sanjida Ahmed
Sanjida Ahmed

Sanjida Ahmed, who graduated from Daffodil International University with a degree in Software Engineering, has worked with SOFTEKO since 2021. She has written over 100 articles on Excel & VBA and, since 2022, has worked as the Project Manager of the Excel Extension Development Project in the Software Development Department. Since starting this software development, she has established an outstanding workflow encompassing a full SDLC. She always tries to create a bridge between her skills and interests in... Read Full Bio

2 Comments
  1. Need to approximately match addresses on 2 sheets and tell me a job # associated
    –sheet 1 has the address (from a trip recorded from fleet software)
    –sheet 2 has the job exact address and job # from our CRM
    –have a cell to put in the formula to match the addy approximately and find the job # and record it.

    • Hi Sue,
      Hope you are doing well.
      You can approximately match addresses on 2 sheets and find a job associated with that address.
      Here, we created a dataset in Sheet1 according to your description.

      Sheet1: Containing Approximate Addresss

      Again, this is Sheet2 containing the exact Address and Job record.

      Sheet2: Containing Address and Job

      Now, to approximately match addresses on 2 sheets and find a job associated with that address use the formula given below in Sheet1 Cell C5.
      =VLOOKUP(B5,Sheet2!$B$5:$C$11,1,TRUE)

      Using VLOOKUP Function to Approximately Match Addresses

      Thanks.

      Regards,
      Arin Islam

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo