Nearest location with XMATCH

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.