How to Use Excel VLOOKUP to Find the Closest Match

In this article, we will demonstrate five examples of how to efficiently implement Excel VLOOKUP to find the closest match in Excel.

As an example of finding the closest match, suppose we want to find the name of a student who achieved 100 marks on the Math test, but no one achieved that mark. So we will identify the student who achieved the closest mark to 100. With the help of the VLOOKUP function, we can find this student/mark easily.

VLOOKUP Function in Excel: Syntax

Generic formula:

Here,

Things to Remember

To avoid errors, keep the following in mind while working with the VLOOKUP function:

• The data in the lookup table must be sorted in ascending order.
• As the range of the data table array to search for the value is fixed, place dollar ($) signs in front of the cell reference numbers of the array table to make the references absolute.
• When working with array values, press Ctrl + Shift + Enter to return results. Pressing only Enter doesn’t work when using array formulas, except in Office365 version.

With that in mind, let’s see the VLOOKUP function in action.

We will demonstrate how to utilize the VLOOKUP function to find a partial match in text, to find the closest value to a predefined value, to find the commission rate of sales personnel, to find the best candidate for a job, and finally, to find an upcoming event date.

Example 1 – Finding a Partial Match in Text Using VLOOKUP

Consider the following data table, where we want to find the population of the “South Region”, but don’t want to write the long name in our search bar. In the indicated lookup box below, we’ll enter a part of the Region name, and in the next cell apply a VLOOKUP formula to return the corresponding Population value.

Our formula is:

Formula Explanation:

The formula looks for -> B6 = South Region Population

In -> $B$5:$C$8 = The Array Data Table.

2 = Returns the value in the same row from the second column of the table array.

TRUE = Returns an approximate match

However, the population of the “South Region” is not returned.

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.

Theoretically, the text string “North Region Population” is equal to “South Region Population”, so the value from the “North Region Population” row is returned.

To return an accurate result from a partial-string match, use a character to represent another character. For instance, in the modified formula below, we concatenate our lookup value with an Asterisk (*) using the Ampersand (&) symbol:

Now, the Population of the “South Region” is correctly returned in the result cell C11.

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

Example 2 –  Finding the Closest Match to the Lookup Value Using VLOOKUP

Now let’s find the closest match to a certain value in a large dataset using VLOOKUP.

Consider the following data table. Let’s find the closest match to our lookup value, 24.

Our formula in cell F5 is:

Explanations:

Look for -> F5 = 24

In -> $C$5:$C$9 = The Array Data Table.

1 = Returns the value from corresponding column number 1.

TRUE = Returns an approximate match.

The formula returns 20, not 25, even though 24 is the closest match to 25. Why is that?

VLOOKUP 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, it starts scanning for the highest value less than the value of 24. When it reaches the value 25, it stops the execution and returns the previous row with the closest value, 20, as the result.

Example 3 – Finding the Commission Rate (Looking for the Closest Sales Value) Using VLOOKUP

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

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. To return the Commission Rate, we need to find the closest match to the Sale Range and 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, using the Commission value from the table on the right (cell G6), the Commission Rate becomes 0.05 (cell D5).

The formula in cell D5 to calculate the commission rate is:

Explanation:

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

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

2 = Returns the value in the same row from the second column of the table array.

TRUE = Returns an approximate match.

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

Drag the formula down using the Fill Handle to apply the formula to the rest of the rows.

Example 4 – Selecting the Candidate with the Closest Experience Using VLOOKUP

Excel’s LOOKUP function can be useful for finding the right person for a job.

Consider the following table:

Suppose we want to find the employee who has the work experience closest to the required value, 6 years of work experience in this case.

• Select an empty cell where the employee name will appear and enter the following formula:

The name of the employee who best fits the job is returned.

Formula Explanation:

E5-$C$5:$C$9 -> The required Experience (6 years) minus the Year of Experience of Each Employee.

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

This subtracts the Years of Experience of each employee from the Required Experience and returns multiple results because it runs through the whole array table, calculates for 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)) -> Returns the minimum value from the 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 in MIN(ABS(E5-C5:C9))) in the array constant (second argument in ABS(E5-$C$5:$C$9)). In this example, we want 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  from the calculation in the previous section, position 5, is the second argument. So, we are searching the value located in position 5 in the $B$5:$B$9 range.

Example 5 – Finding the Next Event Date Using VLOOKUP

Suppose, in the following sample data table, we want to know the next upcoming birthday.

We can simply implement an Excel lookup formula to accomplish this.

The formula for the person’s name or the upcoming event’s name is:

And the formula for the date of the upcoming event is:

Formula Explanation:

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 want 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 $B$5:$B$9 is first the argument and the result from the calculation in the previous section (MATCH(F5,$C$5:$C$9,1)), position 4, is the second argument. So, we are searching for 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 the first argument and the result rom the calculation in the previous section (MATCH(F5,$C$5:$C$9,1)), position 4, is the second argument. So, we are searching for the value located in position 4 in the $C$5:$C$9 range.

Output: Tuesday, December 7, 2021

For the upcoming event date, we simply added one to the cell position returned by the MATCH function, to indicate the next column.

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

Download Practice Workbook

Related Articles

• How to Use VLOOKUP to Find Approximate Match for Text in Excel
• How to Perform VLOOKUP with Wildcard in Excel
• Use VLOOKUP to Find Multiple Values with Partial Match in Excel
• Excel VLOOKUP for Partial Match in Table Array

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