Hi all,
Happy Friday!
A classic formula problem in Excel is how to find the
closest match. This is different from an exact match, or an approximate match, because the idea is to find the match with the
least difference to a target value.
The easiest way to solve this problem is with the XLOOKUP function because one of XLOOKUP's core features is the ability to perform an approximate match on
unsorted data. This sounds very abstract, but we can use this feature to look for a difference of zero between a target value and a set of data, and we don't need to worry about where in the data this match might be. The trick is that we need to calculate the actual differences on-the-fly and use the result as our lookup array. Then we look for a difference of
zero.
This is the approach in the worksheet below, where the formula in cell F5 is:
=XLOOKUP(0,ABS(C5:C16-E5),B5:B16,,1)
[
Download the workbook and read the full explanation]
In older versions of Excel without the XLOOKUP function, you can use an INDEX and MATCH formula, but you have to use a more literal formula. The article above explains both approaches in detail. Download the workbook and try it out yourself.
Excel formulas
The best way to learn Excel formulas is to
practice.
We maintain a list of over 500 working formulas
here.
If you need more structure, we also offer
video training.
Have a great weekend!
Dave
