Please consider supporting this website by buying me a coffee.
Thank you for your consideration!
Updated | November 13, 2024
Lookup the Closest Match
The following formula returns a value from B3:B10 that is closest to the target value in D3...
=XLOOKUP(0,ABS(B3:B10-D3),B3:B10,,1)
For earlier versions of Excel...
=INDEX(B3:B10,MATCH(MIN(ABS(B3:B10-D3)),ABS(B3:B10-D3),0))
Note that the formula needs to be confirmed with CTRL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.
Based on the sample data, the formula returns 45.
Sample Workbook: Download