Explanation
At the core, this formula is a basic INDEX and MATCH formula . However, instead of using the older MATCH function , we are using XMATCH function , which provides a more powerful match mode setting:
=INDEX(location,XMATCH(0,distance,1))
Working from the inside out, we are using the XMATCH function to find the position of the nearest location:
XMATCH(0,distance,1) // find row nearest zero
We do that by setting lookup value to zero (0), lookup array to the distance (C5:C12), and match mode to 1.
A match mode value of 1 tells XMATCH to find an exact match or next largest value . Since lookup value is provided as zero (0), XMATCH will find the first distance greater than zero. A nice benefit of XMATCH – what sets it apart from MATCH – is it doesn’t the lookup array to be sorted. Regardless of order, MATCH will return the first exact match or next largest value.
In the example, XMATCH returns 5, since the smallest distance is 7 (location G), which appears fifth in the list. The formula resolves to:
=INDEX(location,5) // returns "G"
and INDEX returns the fifth item from the named range location (B5:B12), which is “G”.
Note: in the even of a tie, XMATCH will return the first match for tied values.
Get distance
The formula to return the actual distance of the nearest location is almost the same. Instead of giving INDEX the location names, we give INDEX the distances. The formula in F5 is:
=INDEX(distance,XMATCH(0,distance,1)) // returns distance
XMATCH returns the same result as above (5), and INDEX returns 7.
Dynamic Array Formulas are available in Office 365 only.
Explanation
The default behavior of the MATCH function is to match the “next smallest” value in a list that’s sorted in ascending order. Essentially, MATCH moves forward in the list until it encounters a value larger than the lookup value, then drops back to the previous value.
So, when lookup values are sorted in ascending order, both of these formulas return “next smallest”:
=MATCH(value,array) // default
=MATCH(value,array,1) // explicit
However, by setting match type to -1, and sorting lookup values in descending order, MATCH will return the next largest match. So, as seen in the example:
=MATCH(F6,length,-1)
returns 4, since 400 is the next largest match after 364.
Find associated cost
The full INDEX/MATCH formula to retrieve the associated cost in cell F8 is:
=INDEX(cost,MATCH(F6,length,-1))
