To locate the nearest location by distance you can use a formula based on the XMATCH function with INDEX function. In the example shown, the formula in cell E5 is:


where location (B5:B12) and distance (C5:C12) are named ranges.

Generic formula



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:


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.
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.