Although it may not look like it, this is essentially a look-up problem.
The easiest way to solve this problem is with theXLOOKUP function.
Both approaches are explained below.
Then we look for a difference of zero.
Notice many values are negative.
To normalize these values, we use theABS function, which converts the numbers to absolute values.
Finally, we get to thematch_modeargument, which is key to the successful operation of the formula.
By default,match_modeis zero, which means “exact match”.
Instead, the behavior we want is “exact match or next largest value”.
To enable this behavior, we use the number 1 formatch_mode.
(More details on XLOOKUP here).
The next closest match is Stockholm, with a cost of $1,150 and a difference of $50.
Some values are negative because a cost is lower than the number in E5.
This becomes the lookup value inside MATCH.
Note: if there is a tie, this formula will return the first match.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
ABS converts negative numbers to positive numbers, and positive numbers are unaffected.
MIN Function
The Excel MIN function returns the smallest numeric value in the data provided.
The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
Related videos
FILTER function basic example
