How to Use Excel VLOOKUP to Find the Closest Match

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:

Here,

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 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, to find the closest value of a predefined value, to find the commission rate of sales personnel, to find the best candidate for a job and to find the upcoming event date.

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

Suppose you want to extract information of 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.

And according to the generic formula of the VLOOKUP function, our formula was,

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

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

The problem is, 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 the accurate result based on a partial-string match, use a character to represent another character. For instance, we used Asterisk (*) and concatenate our lookup value with it using Ampersand (&) symbol.

So now our formula looks like this,

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 Partial VLOOKUP in Excel (5 Suitable Examples)

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,

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

It gives us the result 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.

Read More: How to Use INDEX and Match for Partial Match (2 Easy Ways)

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.

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.

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,

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.

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

Read More: How to Use IF Function to Find Partial Match in Excel (6 Ways)

Similar Readings

• How to Use Formula for Partial Number Match in Excel (5 Examples)
• Conditional Formatting for Partial Text Match in Excel (9 Examples)
• How to Use VLOOKUP to Find Approximate Match for Text in Excel
• Use VLOOKUP for Partial Match in Excel (4 Suitable Ways)

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 the formulas, but 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 that you want the employee name to appear and in that cell write the following formula,

It will give you the employee name to best fit 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.

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

5. Finding the Next Event Date with VLOOKUP in Excel

Last but not the 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 who’s birthday it is next and when is that.

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

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

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 Use COUNTIF Function for Partial Match 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 life. I hope this article has been very beneficial to you. Feel free to ask any questions if you have regarding the topic.

Related Articles

• Excel SUMIF with Partial Match (3 Ways)
• How to Find Partial Match in Two Columns in Excel (4 Methods)
• Fuzzy Lookup in Excel (With Add-In & Power Query)
• How to Perform Partial Match of String in Excel (8 Easy Methods)
• Highlight Partial Text in Excel Cell (9 Methods)
• Lookup Partial Text Match in Excel (5 Methods)