Find the Closest Match in Excel (Nearest Value)

There are enough functions in Excel to help you if you need to find an exact match for a value from a list.

However, if you’re looking to find the closest matching value from a list, then things may get a little tricky.

Not that it’s impossible though. With Excel, there’s always a workaround.

In this tutorial we will show you two ways to find the closest match (nearest value) in Excel:

Using the INDEX-MATCH Formula
Using the XLOOKUP Formula

Table of Contents

How to Find the Closest Match in Excel

Let us look at a use-case, where finding the closest value would be helpful.

Consider the following dataset:

The above dataset consists of unit prices of different products.

Now say you are looking for a product that is priced closest to a given amount (specified in cell E2 of the screenshot below).

The price could be less than or greater than the given amount, but among all the product prices, it has to be the closest.

To find out which product’s cost is closest to the value in cell E2, you can use one of the following two methods.

Using INDEX-MATCH Formula to Find the Closest Match in Excel

The combination of the INDEX and MATCH functions in Excel is a powerful one. Individually these functions don’t do much.

The MATCH function provides the relative position or ‘index’ of an item in a range of cells, while the INDEX function provides the contents at a given index of a range of cells.

However, when combined together the two formulas can look up a value in a cell from a table and return the corresponding value in another cell in the same row or column.

We can use this combination to find out the index of the price closest to the given value and then return the name of the product corresponding to that price.

But the question remains – how to find the closest value?

For this, we will need a combination of the ABS, MIN, and MATCH functions.

Together, the formula to find the product corresponding to the price closest to the value in E2 is:

{=INDEX(A2:A10,MATCH(MIN(ABS(B2:B10-E2)), ABS(B2:B10-E2),0))}

Note that this is an array formula, so you will need to click on one of the parameters in the formula bar and press the CTRL+SHIFT+Enter shortcut from your keyboard, in order for this function to give the correct result.

When you press the return key, the above formula returns the product corresponding to the value (in the cell range B2:B10) that matches most closely to the value in cell E2.

In this case, the closest value is the second one in the array B2:B10, which is 732.

So the formula returns the corresponding product, “Stacking chairs”.

INDEX MATCH formula to get the closest value

Note that in case there are two values that are equally closed to the lookup value, you’ll get the first one as the result

Explanation of the Formula

The above formula is quite a complex one, so it would make sense to explain it by breaking it down.

Let’s start from the innermost function:

B2:B10-E2

Here, we are subtracting the value in E2 from each value in the range B2:B10.

This is an array operation. Since there are 8 values in the range B2:B10, the above formula returns an array of 8 values containing each of the differences, like this:

{-238;232;-485;458;-478;-451;-493;407;-481}

Notice that some of the above values are negative. This is because those values are less than the value in E2.

ABS(B2:B10-E2)

We want to find the closest value, irrespective of whether the value is less than or greater than the value in E2.

Therefore, we need to convert all the values to their absolute values, so that we can compare the magnitude of the differences, without bothering about the sign.

The above formula returns the following array:

{238;232;485;458;478;451;493;407;481}

MIN(ABS(B2:B10-E2))

Once we get the differences, we can find out which of the numbers is the closest.

In other words, which of them have the smallest difference from the value in cell E2. To find the smallest difference here, we use the MIN function.

The smallest difference in our list of differences is 232.

MATCH(MIN(ABS(B2:B10-E2)), ABS(B2:B10-E2),0)

Since we’ve now got the value with the smallest difference from the value in E2, we can simply use the MATCH function to find out the relative position (or index) of this value in the list of differences.

In our example, the number 232 is the 2^nd value in our list of differences. So the MATCH function returns the value 2.

INDEX(A2:A10,MATCH(MIN(ABS(B2:B10-E2)), ABS(B2:B10-E2),0))

Finally, the INDEX function is used to find the product at index 2 in our table.

In this case, the ‘Stacking Chairs” are the 2nd product (or are at the second index of our list), so the formula returns “Stacking Chairs” at its result.

Note: If there is a tie in the matching, then the formula will return the first match.

Also read: Check If Value is in List in Excel

Using XLOOKUP to Find the Closest Value in Excel (for Excel 365 and Later Versions)

The second method is way simpler and uses the XLOOKUP Function.

The formula used does not require nesting ABS and MIN functions, so it’s easy and quick.

So we can use this function in our original example:

The formula to find the price closest to the value in cell E2 of the above sample dataset is as follows:

=XLOOKUP(E2,B2:B10,A2:A10,,1)

To understand this formula, it is important to first understand the XLOOKUP function, its syntax, and what it does.

The XLOOKUP Function

The XLOOKUP function is a modern and sophisticated replacement to the earlier lookup functions like VLOOKUP, HLOOKUP, etc.

This is because the function is capable of performing both vertical and horizontal lookups.

It also supports different types of matching algorithms, including exact matching, approximate matching as well as partial matching wildcards.

Another advantage over the traditional lookup functions is that it does not require the data to be sorted in order to perform a search.

Note: The XLOOKUP function is only available in Excel 365 and later versions

Syntax for the XLOOKUP Function

The syntax for the XLOOKUP function is as follows:

XLOOKUP (lookup_value, lookup_array, return_array, [not_found], [match_mode], [search_mode])

Here,

lookup_value is the value that you want to lookup or search
lookup_array is the array or range that you want to search in
return_array is the array from which you want to return the value corresponding to the matched item
not_found is the value you want to return if a match is not found. This is an optional parameter and is a blank string by default
match_mode is an integer representing the type of matching you want. Here are the possible values this parameter can have:
- 0 represents an exact match. This is the default value
- 1 represents an exact match or the next larger value
- -1 represents an exact match or the next smaller value
- 2 represents a wildcard match
search_mode is an integer that represents the order in which you want the search to be performed. Here are the possible values this parameter can have:
- 1 represents a search from the first item. This is the default value
- -1 represents a search from the last item
- 2 represents a binary search in ascending order
- -2 represents a binary search in descending order

The XLOOKUP function searches the lookup_array for the lookup_value and returns the value in the return_array that corresponds to the matched value.

It uses the optional parameters in the match_mode and search_mode to perform its searching and matching.

If it doesn’t find an appropriate match, it returns the text specified in the optional not_found parameter. If this parameter is not specified, the function returns a blank.

We used the XLOOKUP function as follows to find the price closest to the value in cell E2 is as follows:

=XLOOKUP(E2,B2:B10,A2:A10,,1)

Notice we kept the 4^th parameter (not_found) blank.

This is because we don’t want to return anything if a match is not found. If you need to, you can change this to any suitable text of your choice.

We also did not specify the 6^th parameter, because we just want to stick with the default search mode and start searching from the top.

The above function, when applied to our sample data, returns the product “Stacked chairs”, corresponding to the closest matching price (732).

XLOOKUP formula to find the closest match in Excel

This is the same result that we got from the first method, but we could get this using a much simpler formula.

Also read: Find Last Occurrence of a Value in a Column in Excel

In this tutorial, we showed you two ways to find the closest match in Excel, with the help of a short practical example.

The first method is complex but can be used in all Excel versions. The second method is simple and quick, but can only be used in newer Excel versions.

Other Excel tutorials you may also find useful: