Загальна формула
=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.
