Quick, clean, and to the point

Nearest location with XMATCH

Excel formula: Nearest location with XMATCH
Generic formula 

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.


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

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.