Finding the Closest Match from a List of Banded Values in Excel Formula

Sometimes, you may need to find the closest match from a list of banded values in Excel. For example, you may have a table that shows the commission rate for different sales ranges, and you want to find the commission rate for a given sales amount. Or you may have a table that shows the tax rate for different income brackets, and you want to find the tax rate for a given income. 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 the values in the list, and find the value that is either equal to or just smaller than the given value. For example, if the given value is 15,000 and the list of values is 5,000, 10,000, 20,000, and 30,000, then the closest match is 10,000, because it is the largest value that is less than or equal to 15,000.

To find the closest match from a list of banded values in Excel, we can use the following formula:

=INDEX (return_range, MATCH (lookup_value, lookup_range, 1))

where:

• return_range is the range of cells that contains the values you want to return, such as commission rates or tax rates.
• lookup_value is the value you want to find the closest match for, such as sales amount or income.
• lookup_range is the range of cells that contains the banded values, such as sales ranges or income brackets.
• 1 is the match type argument that tells Excel to find the largest value that is less than or equal to the lookup value.

This formula works by using the MATCH function to find the relative position of the closest match in the lookup range, and then using the INDEX function to return the corresponding value in the return range.

Procedures

To use the formula to find the closest match from a list of banded values in Excel, follow these steps:

1. Arrange your data in a table, with the banded values in one column and the return values in another column. Make sure the banded values are sorted in ascending order.
2. Enter the lookup value in a cell, such as the sales amount or income you want to find the closest match for.
3. In another cell, enter the formula =INDEX (return_range, MATCH (lookup_value, lookup_range, 1)), and replace the arguments with the appropriate cell references or ranges.
4. Press Enter to get the result. The formula will return the value that is closest to the lookup value from the list of banded values.

Example

Let’s see an example of how to use the formula to find the closest match from a list of banded values in Excel. Suppose you have the following table that shows the commission rate for different sales ranges:

You want to find the commission rate for a salesperson who has made 15,000 in sales. To do this, you can use the following formula:

=INDEX (B2:B6, MATCH (15000, A2:A6, 1))

where:

• B2:B6 is the return range that contains the commission rates.
• 15000 is the lookup value that is the sales amount.
• A2:A6 is the lookup range that contains the sales ranges.
• 1 is the match type argument that tells Excel to find the largest value that is less than or equal to the lookup value.

The formula will return 7%, which is the commission rate for the sales range of 10,001 – 20,000, which is the closest match to the sales amount of 15,000.

Other Approaches

There are other ways to find the closest match from a list of banded values in Excel, such as using the XLOOKUP function or an array formula. Here are some examples of how to use these alternative approaches:

Using XLOOKUP

The XLOOKUP function is a new function in Excel that can perform various types of lookups, including finding the closest match from a list of unsorted values. To use the XLOOKUP function to find the closest match from a list of banded values, you can use the following formula:

=XLOOKUP (lookup_value, lookup_range, return_range,,1)

where:

• lookup_value is the value you want to find the closest match for, such as sales amount or income.
• lookup_range is the range of cells that contains the banded values, such as sales ranges or income brackets.
• return_range is the range of cells that contains the values you want to return, such as commission rates or tax rates.
• 1 is the match mode argument that tells Excel to find the exact match or the next larger value.

This formula works by using the XLOOKUP function to find the value in the lookup range that is either equal to or just larger than the lookup value, and then return the corresponding value in the return range.

For example, if you want to use the XLOOKUP function to find the commission rate for a salesperson who has made 15,000 in sales, using the same table as before, you can use the following formula:

=XLOOKUP (15000, A2:A6, B2:B6,,1)

where:

• 15000 is the lookup value that is the sales amount.
• A2:A6 is the lookup range that contains the sales ranges.
• B2:B6 is the return range that contains the commission rates.
• 1 is the match mode argument that tells Excel to find the exact match or the next larger value.

The formula will return 7%, which is the commission rate for the sales range of 10,001 – 20,000, which is the closest match to the sales amount of 15,000.

Note: The XLOOKUP function is only available in Excel 365 and Excel 2019.

Using an Array Formula

An array formula is a formula that can perform multiple calculations on one or more arrays of values, and return a single result or an array of results. To use an array formula to find the closest match from a list of banded values, you can use the following formula:

=INDEX (return_range, MATCH (MIN (ABS (lookup_value - lookup_range)), ABS (lookup_value - lookup_range), 0))

where:

• return_range is the range of cells that contains the values you want to return, such as commission rates or tax rates.
• lookup_value is the value you want to find the closest match for, such as sales amount or income.
• lookup_range is the range of cells that contains the banded values, such as sales ranges or income brackets.
• 0 is the match type argument that tells Excel to find an exact match.

This formula works by using the ABS function to calculate the absolute differences between the lookup value and the values in the lookup range, and then using the MIN function to find the smallest difference. Then, the formula uses the MATCH function to find the position of the smallest difference in the array of differences, and finally, the formula uses the INDEX function to return the corresponding value in the return range.

To enter an array formula, you need to press Ctrl + Shift + Enter instead of just Enter. Excel will automatically enclose the formula in curly braces {} to indicate that it is an array formula.

For example, if you want to use an array formula to find the commission rate for a salesperson who has made 15,000 in sales, using the same table as before, you can use the following formula:

=INDEX (B2:B6, MATCH (MIN (ABS (15000 - A2:A6)), ABS (15000 - A2:A6), 0))

where:

• B2:B6 is the return range that contains the commission rates.
• 15000 is the lookup value that is the sales amount.
• A2:A6 is the lookup range that contains the sales ranges.
• 0 is the match type argument that tells Excel to find an exact match.

The formula will return 7%, which is the commission rate for the sales range of 10,001 – 20,000, which is the closest match to the sales amount of 15,000.

Note: You need to press Ctrl + Shift + Enter to enter this formula as an array formula. Excel will automatically enclose the formula in curly braces {}.