Generische Formel
=INDEX(location,XMATCH(0,distance,1))
Zusammenfassung
Um den nächstgelegenen Ort nach Entfernung zu finden, können Sie eine Formel verwenden, die auf der XMATCH-Funktion mit der INDEX-Funktion basiert. In dem gezeigten Beispiel lautet die Formel in Zelle E5:
=INDEX(location,XMATCH(0,distance,1))
Dabei werden Ort (B5: B12) und Entfernung (C5: C12) als Bereiche bezeichnet.
Erläuterung
Im Kern ist diese Formel eine grundlegende INDEX- und MATCH-Formel. Anstelle der älteren MATCH-Funktion verwenden wir jedoch die XMATCH-Funktion, die eine leistungsfähigere Einstellung für den Übereinstimmungsmodus bietet:
=INDEX(location,XMATCH(0,distance,1))
Wir arbeiten von innen nach außen und verwenden die XMATCH-Funktion, um die Position des nächstgelegenen Standorts zu ermitteln:
XMATCH(0,distance,1) // find row nearest zero
Dazu setzen wir den Lookup-Wert auf Null (0), das Lookup-Array auf den Abstand (C5: C12) und den Match-Modus auf 1.
Ein Übereinstimmungsmoduswert von 1 weist XMATCH an, eine genaue Übereinstimmung oder den nächstgrößeren Wert zu finden. Da der Suchwert als Null (0) angegeben wird, findet XMATCH den ersten Abstand größer als Null. Ein netter Vorteil von XMATCH - was es von MATCH unterscheidet - ist, dass es nicht das zu sortierende Lookup-Array ist. Unabhängig von der Reihenfolge gibt MATCH die erste exakte Übereinstimmung oder den nächstgrößeren Wert zurück.
Im Beispiel gibt XMATCH 5 zurück, da die kleinste Entfernung 7 (Position G) beträgt, was in der Liste an fünfter Stelle steht. Die Formel lautet:
=INDEX(location,5) // returns "G"
und INDEX gibt das fünfte Element aus der benannten Bereich Lage (B5: B12), die „G“.
Hinweis: Bei einem Gleichstand gibt XMATCH die erste Übereinstimmung für gebundene Werte zurück.
Entfernung bekommen
Die Formel zur Rückgabe der tatsächlichen Entfernung zum nächstgelegenen Standort ist nahezu identisch. Anstatt INDEX die Ortsnamen zu geben, geben wir INDEX die Entfernungen. Die Formel in F5 lautet:
=INDEX(distance,XMATCH(0,distance,1)) // returns distance
XMATCH gibt das gleiche Ergebnis wie oben (5) zurück und INDEX gibt 7 zurück.
