Looking Up an Item Name Based on Unsorted Values

Paul is developing a worksheet that will be used by others in his office. In cell D1 he has a numeric lookup value. He needs to find, within column A, the closest match to that numeric value and then return the corresponding item name from column B. Paul cannot count on the values in column A or column B being sorted, nor can he guarantee there won't be blanks in the columns. He wonders what formula he can use to return the item name (column B) whose value (column A) is closest to his target (D1).

The answer to Paul's question depends, primarily, on what is meant by "closest." There are three possible meanings—less than or equal to D1, greater than or equal to D1, or both.

If you want to figure out what value is less than or equal to D1, then you can use XLOOKUP to return the desired name:

=XLOOKUP($D$1,A:A,B:B,,-1)

The XLOOKUP function is only available for those using Excel 2021 or later. If you are using an older version of Excel, then you will need to use the INDEX and MATCH functions in an array formula:

=INDEX(B2:B350,MATCH(MAX(IF(A2:A350<=$D$1,A2:A350)),A2:A350,0))

Since this is an array formula, you'll need to use Ctrl+Shift+Enter if you are using Excel 2016 or earlier. If you want to return the closest name where the value in column A is greater than or equal to D1, then simply replace <= in the formula with >=.

If you are using Excel 2021 or later, then you can swap out the -1 in the XLOOKP function with 1, as shown here:

=XLOOKUP($D$1,A:A,B:B,,1)

If you want to find the name where the closest value can be either above or below the value in D1, then the formula becomes a bit more complex:

=XLOOKUP(MIN(ABS(A2:A350-$D$1)),ABS(A2:A350-$D$1),B2:B350)

The formula first uses the ABS function to find the positive distance between the value in column A and the value in D1, and then uses MIN to figure out the lowest of those values. This is then used with XLOOKUP to find the corresponding name from column B.

If you are using a version of Excel before Excel 2021, then the following—again, entered as an array formula—will provide the same result:

=INDEX(B2:B350,MATCH(MIN(ABS(A2:A350-$D$1)),ABS(A2:A350-$D$1),0))

There is one other thing to note about these formulas. In the formulas that use XLOOKUP, notice that the entire column A and B are referenced. In the formulas that use INDEX and MATCH, a specific range of rows 2 through 350 is referenced for columns A and B. This range specification, starting with row 2, is necessary if row 1 contains column headings. This accommodation is not necessary with XLOOKUP.

There is one other thing that needs to be considered: It is possible for multiple values in column A to be equally close to the value in D1. The formulas discussed so far don't return multiple values; they only return the name corresponding with the first closest match. If you want to return multiple names, then you can rely on the FILTER function, as in these formulas:

=FILTER(B2:B350,ABS($D$1-A2:A350)=MIN(ABS($D$1-A2:A350)))
=LET(X,ABS($D$1-A2:A350), FILTER(B2:B350,X=MIN(X)))

The difference between the two renditions of this formula is that the second employs the LET function to shorten things just a bit. Either formula will work, provided you are using at least Excel 2021. If multiple values are returned, they are spilled downwards below the formula. If you prefer to have the results be in a single cell, separated by commas, then you can add the ARRAYTOTEXT function to the mix:

=ARRAYTOTEXT(FILTER(B2:B350,ABS($D$1-A2:A350)=MIN(ABS($D$1-A2:A350))),0)
=LET(X,ABS($D$1-A2:A350), ARRAYTOTEXT(FILTER(B2:B350,X=MIN(X)),0))

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (14007) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.