On LinkedIn recently someone posted an Excel formula solution lamenting that it was long and complex. That of course was a challenge to me to simplify it.

The idea behind the problem was to find a way to show the closest match to a value.

The image below has the problem.

The input value is 18. We want to find the row in the table with the closest match to the value – in this case row 2. Josh with 14 is the closest match to 18.

Note: the list is not sorted, so we can’t use any lookup functions.

The proposed solution used dynamic arrays. My solution also used dynamic arrays.

My thought process was that the list isn’t sorted but we could use the SORTBY function to create a sorted list based on the difference to the value.

One issue is we don’t care if the difference is positive or negative. This means a difference of -3 is the same as 3. So that immediately got me thinking of the ABS function which converts negatives to positives.

The SORTBY function allows us to perform calculations on the column to sort by.

So my initial formula in cell E4 was.

=SORTBY(A2:B5,ABS(B2:B5-F1))

See image below.

This formula spills to the rows below and to the columns to the right. This re-sorts the list based on the difference to 18. As you can see the closest match is listed first.

We don’t need to list the whole table, we only want the closet match, the first row.

To convert the spilled range to a single row we can use the INDEX function which works well with spilled ranges.

The final formula for cell E4 is.

=INDEX(SORTBY(A2:B5,ABS(B2:B5-F1)),1)

The INDEX takes the whole table and by using ,1 on the end simply extracts the first row – job done.

Note this technique doesn’t list multiple entries if there are duplicates or other numbers that are equally close.