Обща формула
=INDEX(location,XMATCH(0,distance,1))
Обобщение
За да намерите най-близкото местоположение по разстояние, можете да използвате формула, базирана на функцията XMATCH с функция INDEX. В показания пример формулата в клетка E5 е:
=INDEX(location,XMATCH(0,distance,1))
където местоположението (B5: B12) и разстоянието (C5: C12) са наречени диапазони.
Обяснение
В основата си тази формула е основна формула INDEX и MATCH. Въпреки това, вместо да използваме по-старата функция 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 - е, че масивът за търсене не се сортира. Независимо от поръчката, MATCH ще върне първото точно съвпадение или следващата най-голяма стойност.
В примера XMATCH връща 5, тъй като най-малкото разстояние е 7 (местоположение G), което се появява на пето място в списъка. Формулата разрешава:
=INDEX(location,5) // returns "G"
и INDEX връща петия елемент от посоченото местоположение на диапазона (B5: B12), което е "G".
Забележка: в равен резултат на равенството XMATCH ще върне първото съвпадение за равенства.
Вземете разстояние
Формулата за връщане на действителното разстояние от най-близкото местоположение е почти еднаква. Вместо да даваме на INDEX имената на местоположенията, ние даваме на INDEX разстоянията. Формулата във F5 е:
=INDEX(distance,XMATCH(0,distance,1)) // returns distance
XMATCH връща същия резултат като по-горе (5), а INDEX връща 7.
