Using VLOOKUP Approximate Match – Examples
The VLOOKUP Function is, arguably, one of the most useful built-in Excel functions. It allows you to search for specific information in a table or range.
The VLOOKUP function can be confusing at first for some users since you can specify either an Exact or Approximate Match, and it may not be clear what the difference is.
In this tutorial, we are going to go over what the VLOOKUP Function does and when to use VLOOKUP Approximate Match.
Additionally, we will also briefly cover using the INDEX and MATCH combination formula for an Approximate Match.
So let’s get started.
VLOOKUP in Context
VLOOKUP stands for vertical lookup because it’s used to search for data in vertically arranged datasets. If your data has a horizontal layout then you should use the HLOOKUP Function instead.
The VLOOKUP Function is available in both older and newer versions of Microsoft Excel. It’s quite widely used for lookups, so it’s advisable to have an in-depth knowledge of this function.
Note: If you are a Microsoft Office 365 subscriber, then you can use the XLOOKUP function for both vertically and horizontally arranged datasets.
You use the VLOOKUP Function to find matching data from another column, for a certain input value.
For example, you could use VLOOKUP to find a student name from a table when you are given the corresponding student ID. You could also use it to search for the appropriate student grade when given a student’s score.
Let’s look at the syntax of the VLOOKUP Function to gain a better understanding of the arguments needed:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) where:
- lookup_value refers to the value (number or text) that you would like the function to search for. It can be a cell reference, a value that you type in directly, or the result returned by another function. This is a required argument.
- table_array refers to the table or range where the lookup value and corresponding matching value is located. This is a required argument.
- col_index_num refers to the number of the column in the table or range from which Excel will retrieve the matching value. The numbering of the columns starts from 1, and goes from left to right. This is a required argument.
- range_lookup refers to whether the function will search for an Exact or Approximate Match. If you specify TRUE, which is the default, then Excel will perform an Approximate Match. If you specify FALSE, then Excel will perform an Exact Match.
For an Approximate Match, if Excel does not find an exact corresponding matching value, then it searches for the largest value that is smaller than the lookup_value.
If you want to specify an Approximate Match, then the column containing the lookup values must be sorted in ascending order.
For an Exact Match, Excel searches for a value that is the same as the lookup value, and if this isn’t found, then the #N/A error is returned.
This is an optional argument.
Note: The standard VLOOKUP is case-insensitive.
How to Use VLOOKUP Approximate Match to Find Grades
In our first example, we are going to use VLOOKUP Approximate Match to find a grade when given a student score.
Below, we have a list of students and their respective Math scores. We also have a reference table that shows us how the score is allocated for each grade.
We can see that many of the students didn’t obtain the exact score shown in the reference table. This should be a clue that, in this situation, we will need an Approximate Match.
These are our steps:
- In cell C2, insert the following formula:
=VLOOKUP(B2,$G$5:$H$10,2,TRUE)
- Using the fill handle, drag the formula down the column.
Explanation of the formula:
Let’s look at the individual components of our formula in order to understand it better.
=VLOOKUP(B2,$G$5:$H$10,2,TRUE)
Our lookup_value is the student’s score in cell B2, which in this case is 46. We have used a relative reference since we want the formula to update as we drag down. This is the value we want to find a grade for.
VLOOKUP will search for this value in the left-most column of our reference table which is the Score column and return a value from the column to the right, which is the Grade column.
Our table_array is cell range $G$5:$H$10, this is our reference table. We can see that the scores are sorted in ascending order from lowest to highest. We used an Absolute reference for our table_array since we want to drag the formula down the column and keep the reference consistent.
We will retrieve the grade, which is the second column in our reference table. Hence, we specify 2 as the col_index_num.
Since we need an Approximate Match, we specified that our range_lookup value is TRUE.
When this parameter is set to TRUE, the function looks for an exact match first. So it looks for 46, in our reference table since that was the lookup_value.
As you can see, however, the reference table does not have an exact match; there is no 46 in the Score column.
So what the function does is look for the largest value that is smaller than 46, in this case, it is 45. So we can see that for a score of 45, then a grade of E is given. So, the function returns a grade of E.
Note: When you do have the same value in the reference table as for cell B14, the function returns the matching grade and not one lower.
Also read: Find the Closest Match in Excel (Nearest Value)
How to Use VLOOKUP Approximate Match to Find Simple Tax Rates
Another popular use of the VLOOKUP Approximate Match that you may come across is for looking up simple tax rates or sales commissions.
Below, we have an Excel Table which contains a list of employees and their respective incomes.
We want to use VLOOKUP Approximate Match to populate column C, with the appropriate tax rate for each employee.
These are our steps:
- In cell C2, insert the following formula:
=VLOOKUP([@Income],$G$4:$H$12,2,TRUE)
- Drag the formula down the column using the fill handle.
- Select range C2:C16. Go to the Home Tab, and in the Number Group select %.
- You should see the following.
Explanation of the formula:
Let’s look at the individual parts of our formula in order to gain a better understanding.
=VLOOKUP([@Income],$G$4:$H$12,2,TRUE)
Since we have an Excel Table, we are using structured table references to refer to cell B2.
So after one selects the cell B2, which is our lookup_value, the structured reference should automatically appear. The income in cell B2, of employee Thomas Clayt is $13,337.00.
Our table_array refers to our reference table, which is $G$4:$H$12. Income is sorted in ascending order from the lowest income to the highest. We used an absolute reference since we want the table_array to stay the same as we drag the formula down the column.
We will retrieve the tax rate, which is the second column in our reference table. Hence, we specify 2 as the col_index_num.
Again we want an Approximate Match, since most of our Employee income column in our Excel Table, does not exactly match our Income column in the reference table. So we specify TRUE.
So what the function does is look for $13,337.00, in our Income column of the reference table, and it doesn’t find an exact match.
As a result, the function looks for the first value that is smaller than $13,337.00, and finds this to be $11,000.00
The matching tax rate for $11,000.00 is 9%. So 9% is returned since that is the closest match.
Note: For all incomes larger than $32,000.00, as in the case of Jeanne Walls, the tax rate of 15% is returned by the function.
Also read: VLOOKUP Not Working – 7 Possible Reasons + Fix!
How to Use the INDEX and MATCH Functions for an Approximate Match
The INDEX and MATCH combination is usually utilized for more sophisticated purposes such as case-sensitive, multiple criteria, or left lookups.
You can also use the INDEX and MATCH combination for an Approximate Match.
We are going to use the same tax rate example as we used above, but this time, instead of VLOOKUP, we will utilize the INDEX and MATCH combination.
These are our steps:
- In cell C2, insert the following formula:
=INDEX($H$4:$H$12,MATCH([@Income],$G$4:$G$12,1))
- Drag the formula down the column using the fill handle.
- Select range C2:C16. Go to the Home Tab, and in the Number Group select %.
- You should now see the following.
Explanation of the formula:
We will have to break our formula down into layers to understand it better.
We’ll start with the innermost layer given by the MATCH Function.
- MATCH([@Income],$G$4:$G$12,1)
The MATCH Function is used to find the relative position of a specified value, in a range.
Let’s quickly review the syntax of the MATCH Function.
=MATCH(lookup_value, lookup_array, [match_type]) where:
- lookup_value is the value that you want to look up and find the position of. This is a required argument.
- lookup_array refers to the range of interest where Excel will search for the lookup_value. This is a required argument.
- match_type refers to the type of search to be performed. If a value of 1 is specified or this argument is omitted, then Excel finds the largest value that is less than or equal to the lookup_value.
The lookup_array must be sorted in ascending order for this type of match to work.
If a value of 0 is specified, then Excel looks for an exact match equal to the lookup_value.
If a value of -1 is specified then Excel finds the smallest value that is greater than or equal to the lookup_value.
The lookup_array must be sorted in descending order in this case.
This is an optional argument.
The MATCH Function searches for the value in cell B2 which is $13,337.00, from the incomes listed in range $G$4:$G$12. Our lookup_array is the range $G$4:$G$12.
Since we want the same type of Approximate Match as that of our VLOOKUP example, we specified a value of 1 as the match_type.
This means that the MATCH Function will find the largest value that is less than or equal to the value in cell B2, from range $G$4:$G$12 and return its position.
The position returned is 4, since this is the position of the $11,000.00 income.
- INDEX($H$4:$H$12,MATCH([@Income],$G$4:$G$12,1))
The INDEX Function is used to return a value when given a location in an array or range. In this case the location is given by the MATCH Function.
The INDEX Function then looks at the range $H$4:$H$12, and using the position obtained from the Match Function of 4, retrieves 9%.
And there you have it.
In this tutorial, we have gone through how to use VLOOKUP Approximate Match which should help clear any confusion.
Always remember if you specify TRUE as the range_lookup value then you will be able to conduct an Approximate Match. However, if you specify FALSE, then it is an Exact match.
You can also use the more complex but advanced INDEX and MATCH combination to perform an Approximate Match.
I hope you enjoyed this tutorial. Please feel free to comment below.
Other articles you may also like:
Thank You for sharing your knowledge.