Formula generică
=INDEX(location,XMATCH(0,distance,1))
rezumat
Pentru a localiza cea mai apropiată locație în funcție de distanță, puteți utiliza o formulă bazată pe funcția XMATCH cu funcția INDEX. În exemplul prezentat, formula din celula E5 este:
=INDEX(location,XMATCH(0,distance,1))
unde locația (B5: B12) și distanța (C5: C12) sunt denumite intervale.
Explicaţie
La bază, această formulă este o formulă de bază INDEX și MATCH. Cu toate acestea, în loc să folosim funcția MATCH mai veche, folosim funcția XMATCH, care oferă o setare mai puternică a modului de potrivire:
=INDEX(location,XMATCH(0,distance,1))
Lucrând din interior spre exterior, folosim funcția XMATCH pentru a găsi poziția celei mai apropiate locații:
XMATCH(0,distance,1) // find row nearest zero
Facem acest lucru setând valoarea de căutare la zero (0), matricea de căutare la distanță (C5: C12) și modul de potrivire la 1.
O valoare a modului de potrivire 1 îi spune lui XMATCH să găsească o potrivire exactă sau următoarea cea mai mare valoare. Deoarece valoarea de căutare este furnizată ca zero (0), XMATCH va găsi prima distanță mai mare decât zero. Un beneficiu frumos al XMATCH - ceea ce îl diferențiază de MATCH - este că nu matricea de căutare trebuie sortată. Indiferent de comandă, MATCH va returna prima potrivire exactă sau următoarea cea mai mare valoare.
În exemplu, XMATCH returnează 5, deoarece cea mai mică distanță este 7 (locația G), care apare pe locul cinci în listă. Formula se rezolvă astfel:
=INDEX(location,5) // returns "G"
iar INDEX returnează cel de-al cincilea element din locația intervalului numit (B5: B12), care este „G”.
Notă: la egalitate, XMATCH va returna primul meci pentru valori la egalitate.
Obține distanță
Formula pentru a returna distanța reală a celei mai apropiate locații este aproape aceeași. În loc să oferim INDEX numele locațiilor, oferim INDEX distanțele. Formula din F5 este:
=INDEX(distance,XMATCH(0,distance,1)) // returns distance
XMATCH returnează același rezultat ca mai sus (5), iar INDEX returnează 7.
