where location (B5:B12) and distance (C5:C12) are named ranges. Working from the inside out, we are using the XMATCH function to find the position of the nearest location: 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: 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: XMATCH returns the same result as above (5), and INDEX returns 7.
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.
