Общая формула
=INDEX(location,XMATCH(0,distance,1))
Резюме
Чтобы найти ближайшее местоположение по расстоянию, вы можете использовать формулу, основанную на функции XMATCH с функцией INDEX. В показанном примере формула в ячейке E5:
=INDEX(location,XMATCH(0,distance,1))
где местоположение (B5: B12) и расстояние (C5: C12) являются именованными диапазонами.
Объяснение
По сути, эта формула является базовой формулой ИНДЕКС и ПОИСКПОЗ. Однако вместо использования старой функции MATCH мы используем функцию XMATCH, которая обеспечивает более мощную настройку режима сопоставления:
=INDEX(location,XMATCH(0,distance,1))
Работая изнутри, мы используем функцию XMATCH, чтобы найти положение ближайшего местоположения:
XMATCH(0,distance,1) // find row nearest zero
Мы делаем это, устанавливая значение поиска на ноль (0), массив поиска на расстояние (C5: C12) и режим сопоставления на 1.
Значение режима сопоставления 1 указывает XMATCH найти точное совпадение или следующее по величине значение. Поскольку значение поиска предоставляется как ноль (0), XMATCH найдет первое расстояние больше нуля. Хорошее преимущество XMATCH - то, что отличает его от MATCH - это то, что он не сортирует массив поиска. Независимо от порядка, ПОИСКПОЗ вернет первое точное совпадение или следующее по величине значение.
В этом примере XMATCH возвращает 5, так как наименьшее расстояние равно 7 (местоположение G), которое появляется пятым в списке. Формула разрешает:
=INDEX(location,5) // returns "G"
а INDEX возвращает пятый элемент из местоположения именованного диапазона (B5: B12), который равен «G».
Примечание: в случае ничьей XMATCH вернет первое совпадение для связанных значений.
Получите расстояние
Формула для возврата фактического расстояния до ближайшего местоположения почти такая же. Вместо того, чтобы давать ИНДЕКС названия местоположений, мы даем ИНДЕКС расстояния. Формула в F5:
=INDEX(distance,XMATCH(0,distance,1)) // returns distance
XMATCH возвращает тот же результат, что и выше (5), а INDEX возвращает 7.
