Sometimes, you may need to find the closest match for a given value from a list of banded values in Excel. For example, you may want to assign a commission rate based on the sales amount, or find the best candidate based on the years of experience. In this article, we will show you how to use Excel formulas to find the closest match from a list of banded values in different scenarios.
The basic idea of finding the closest match from a list of banded values is to compare the given value with each value in the list, and find the smallest difference. Then, we can return the corresponding value from another column or range. For example, if we have a list of sales amounts and commission rates as shown below, and we want to find the commission rate for a sales amount of $15,000, we can use the following steps:
- Subtract the sales amount of $15,000 from each value in the list, and get the absolute value of the difference. This will give us an array of differences, such as {5000; 0; 5000; 10000; 15000}.
- Find the minimum value in the array of differences, which is 0 in this case.
- Find the position of the minimum value in the array, which is 2 in this case.
- Return the value in the second position of the commission rate column, which is 5% in this case.
Procedures
There are different ways to implement the above steps in Excel formulas, depending on the version of Excel you are using and the structure of your data. Here are some common methods:
Using VLOOKUP
The VLOOKUP function can perform an approximate match on a sorted list of values, and return the corresponding value from another column. The syntax of the VLOOKUP function is:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
where:
- lookup_value is the value you want to match.
- table_array is the range of cells that contains the list of values and the corresponding values.
- col_index_num is the column number in the table_array from which you want to return a value.
- range_lookup is an optional argument that specifies whether you want an exact match or an approximate match. If you omit this argument or set it to TRUE, the function will perform an approximate match. If you set it to FALSE, the function will perform an exact match.
To use the VLOOKUP function to find the closest match from a list of banded values, you need to make sure that the list of values is sorted in ascending order, and use the approximate match option. For example, using the same data as above, the formula to find the commission rate for a sales amount of $15,000 is:
=VLOOKUP(15000, B2:C6, 2, TRUE)
This formula will return 5%, as explained in the basic theory section.
Using INDEX and MATCH
The INDEX and MATCH functions can work together to perform a lookup on an unsorted list of values, and return the corresponding value from another column or range. The syntax of the INDEX function is:
=INDEX(array, row_num, [column_num])
where:
- array is the range of cells that contains the values you want to return.
- row_num is the row number in the array from which you want to return a value.
- column_num is an optional argument that specifies the column number in the array from which you want to return a value.
The syntax of the MATCH function is:
=MATCH(lookup_value, lookup_array, [match_type])
where:
- lookup_value is the value you want to match.
- lookup_array is the range of cells that contains the list of values you want to match against.
- match_type is an optional argument that specifies how you want to match the lookup_value. If you omit this argument or set it to 1, the function will perform an approximate match and find the largest value that is less than or equal to the lookup_value. If you set it to 0, the function will perform an exact match. If you set it to -1, the function will perform an approximate match and find the smallest value that is greater than or equal to the lookup_value.
To use the INDEX and MATCH functions to find the closest match from a list of banded values, you need to use an array formula that calculates the array of differences, finds the minimum value, and returns the corresponding value from another column or range. For example, using the same data as above, the formula to find the commission rate for a sales amount of $15,000 is:
=INDEX(C2:C6, MATCH(MIN(ABS(B2:B6-15000)), ABS(B2:B6-15000), 0))
Note that this is an array formula, so you need to press Ctrl + Shift + Enter to enter it. This formula will return 5%, as explained in the basic theory section.
Using XLOOKUP
The XLOOKUP function is a new function in Excel that can perform a lookup on any list of values, and return the corresponding value from another column or range. The syntax of the XLOOKUP function is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
where:
- lookup_value is the value you want to match.
- lookup_array is the range of cells that contains the list of values you want to match against.
- return_array is the range of cells that contains the values you want to return.
- if_not_found is an optional argument that specifies what you want to return if the lookup_value is not found. If you omit this argument, the function will return #N/A.
- match_mode is an optional argument that specifies how you want to match the lookup_value. If you omit this argument or set it to 0, the function will perform an exact match. If you set it to 1, the function will perform an exact match or next larger value. If you set it to -1, the function will perform an exact match or next smaller value. If you set it to 2, the function will perform a wildcard match.
- search_mode is an optional argument that specifies the direction of the search. If you omit this argument or set it to 1, the function will search from the first to the last value. If you set it to -1, the function will search from the last to the first value. If you set it to 2, the function will perform a binary search on a sorted list of values. If you set it to -2, the function will perform a binary search on a reverse-sorted list of values.
To use the XLOOKUP function to find the closest match from a list of banded values, you need to use the match_mode argument that performs an exact match or next smaller value. For example, using the same data as above, the formula to find the commission rate for a sales amount of $15,000 is:
=XLOOKUP(15000, B2:B6, C2:C6, , -1)
This formula will return 5%, as explained in the basic theory section.
Explanation
In this section, we will provide a comprehensive explanation of how to find the closest match from a list of banded values in Excel, using a detailed example with real data. We will use the INDEX and MATCH method, as it is the most versatile and compatible with all versions of Excel.
Scenario
Suppose you are a recruiter and you have a list of candidates for a job position that requires 3 years of experience. You want to find the candidate that has the closest experience to the requirement, and also see their name, email, and phone number. The data is shown below:
| Name | Phone | Experience | |
|---|---|---|---|
| Alice | alice@example.com | 123-456-7890 | 2.5 |
| Bob | bob@example.com | 234-567-8901 | 3.2 |
| Charlie | charlie@example.com | 345-678-9012 | 2.8 |
| David | david@example.com | 456-789-0123 | 4.1 |
| Eve | eve@example.com | 567-890-1234 | 3.5 |
| Frank | frank@example.com | 678-901-2345 | 2.9 |
Calculation
To find the candidate that has the closest experience to 3 years, we can use the following array formula:
=INDEX(A2:A7, MATCH(MIN(ABS(D2:D7-3)), ABS(D2:D7-3), 0))
This formula will return Bob, as he has the closest experience to 3 years, with a difference of only 0.2 years. Let’s see how this formula works step by step:
- First, we subtract 3 from each value in the experience column, and get the absolute value of the difference. This will give us an array of differences, such as {0.5; 0.2; 0.2; 1.1; 0.5; 0.1}.
- Next, we find the minimum value in the array of differences, which is 0.1 in this case.
- Then, we find the position of the minimum value in the array, which is 6 in this case.
- Finally, we return the value in the sixth position of the name column, which is Frank in this case.
Explanation
To explain the result of the formula, we can use the following sentence:
The candidate that has the closest experience to 3 years is Frank, as he has an experience of 2.9 years, which is only 0.1 years less than the requirement.
Excel Table
To display the data and the formula in an Excel table, we can use the following markdown code:
| Name | Phone | Experience | Difference | Formula | |
|---|---|---|---|---|---|
| Alice | alice@example.com | 123-456-7890 | 2.5 | 0.5 | |
| Bob | bob@example.com | 234-567-8901 | 3.2 | 0.2 | |
| Charlie | charlie@example.com | 345-678-9012 | 2.8 | 0.2 | |
| David | david@example.com | 456-789-0123 | 4.1 | 1.1 | |
| Eve | eve@example.com | 567-890-1234 | 3.5 | 0.5 | |
| Frank | frank@example.com | 678-901-2345 | 2.9 | 0.1 | |
=INDEX(A2:A7, MATCH(MIN(ABS(D2:D7-3)), ABS(D2:D7-3), 0)) |
This will produce the following table:
| Name | Phone | Experience | Difference | Formula | |
|---|---|---|---|---|---|
| Alice | alice@example.com | 123-456-7890 | 2.5 | 0.5 | |
| Bob | bob@example.com | 234-567-8901 | 3.2 | 0.2 | |
| Charlie | charlie@example.com | 345-678-9012 | 2.8 | 0.2 | |
| David | david@example.com | 456-789-0123 | 4.1 | 1.1 | |
| Eve | eve@example.com | 567-890-1234 | 3.5 | 0.5 | |
| Frank | frank@example.com | 678-901-2345 | 2.9 | 0.1 | |
=INDEX(A2:A7, MATCH(MIN(ABS(D2:D7-3)), ABS(D2:D7-3), 0)) |
The formula in the last cell will return Frank, as shown in the calculation section.
Other Approaches
Besides the methods we have discussed so far, there are some other possible approaches to find the closest match from a list of banded values in Excel. Here are some examples:
- Using SUMPRODUCT and MIN: This method uses the SUMPRODUCT function to calculate the array of differences, and the MIN function to find the minimum value. Then, it uses another SUMPRODUCT function to return the corresponding value from another column or range. For example, using the same data as above, the formula to find the commission rate for a sales amount of $15,000 is:
=SUMPRODUCT(C2:C6, --(ABS(B2:B6-15000)=MIN(ABS(B2:B6-15000))))
This formula will also return 5%, as explained in the basic theory section.
- Using AGGREGATE: This method uses the AGGREGATE function to perform an array operation that calculates the array of differences, and finds the minimum value. Then, it uses another AGGREGATE function to return the corresponding value from another column or range. For example, using the same data as above, the formula to find the commission rate for a sales amount of $15,000 is:
=AGGREGATE(15, 6, C2:C6/(ABS(B2:B6-15000)=AGGREGATE(15, 6, ABS(B2:B6-15000), 1)), 1)
This formula will also return 5%, as explained in the basic theory section.
