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** = R*ange of the Array Data Table*

**2** = T*o return the value in the same row from the second column of the table array*

**TRUE** = T*o get the approximate match*

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)`

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** = R*ange of the Array Data Table*

**1** = T*o return the value from the corresponding column*

**TRUE** = T*o get the approximate match*

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.

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,

`=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.

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))`

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.

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)`

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

- [Fixed!] Excel VLOOKUP Partial Match Not Working
- 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**

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

approximatelymatchaddresseson2sheetsandfindajobassociated with thataddress.Here, we created a dataset in

Sheet1according to your description.Again, this is

Sheet2containing the exactAddressandJobrecord.Now, to

approximately match addresseson2 sheetsandfindajobassociated with thataddressuse the formula given below inSheet1CellC5.`=VLOOKUP(B5,Sheet2!$B$5:$C$11,1,TRUE)`

Thanks.

Regards,

Arin Islam