Formula umum
=INDEX(location,XMATCH(0,distance,1))
Ringkasan
Untuk menemukan lokasi terdekat dengan jarak Anda dapat menggunakan rumus yang didasarkan pada fungsi XMATCH dengan fungsi INDEX. Dalam contoh yang diperlihatkan, rumus di sel E5 adalah:
=INDEX(location,XMATCH(0,distance,1))
di mana lokasi (B5: B12) dan jarak (C5: C12) diberi nama rentang.
Penjelasan
Pada intinya, rumus ini adalah rumus INDEX dan MATCH dasar. Namun, alih-alih menggunakan fungsi MATCH yang lebih lama, kami menggunakan fungsi XMATCH, yang menyediakan pengaturan mode pencocokan yang lebih kuat:
=INDEX(location,XMATCH(0,distance,1))
Bekerja dari dalam ke luar, kami menggunakan fungsi XMATCH untuk menemukan posisi lokasi terdekat:
XMATCH(0,distance,1) // find row nearest zero
Kami melakukannya dengan mengatur nilai pencarian ke nol (0), array pencarian ke jarak (C5: C12), dan mode pencocokan ke 1.
Nilai mode pencocokan 1 memberi tahu XMATCH untuk menemukan kecocokan tepat atau nilai terbesar berikutnya. Karena nilai pencarian diberikan sebagai nol (0), XMATCH akan menemukan jarak pertama lebih besar dari nol. Manfaat bagus dari XMATCH - yang membedakannya dari MATCH - adalah bukan array pencarian yang akan diurutkan. Terlepas dari urutannya, MATCH akan mengembalikan kecocokan tepat pertama atau nilai terbesar berikutnya.
Dalam contoh, XMATCH mengembalikan 5, karena jarak terkecil adalah 7 (lokasi G), yang muncul kelima dalam daftar. Rumusnya memutuskan untuk:
=INDEX(location,5) // returns "G"
dan INDEX mengembalikan item kelima dari lokasi jangkauan bernama (B5: B12), yaitu "G".
Catatan: di even seri, XMATCH akan mengembalikan kecocokan pertama untuk nilai seri.
Dapatkan jarak
Rumus untuk mengembalikan jarak sebenarnya dari lokasi terdekat hampir sama. Alih-alih memberi INDEX nama lokasi, kami memberi INDEX jarak. Rumus di F5 adalah:
=INDEX(distance,XMATCH(0,distance,1)) // returns distance
XMATCH mengembalikan hasil yang sama seperti di atas (5), dan INDEX mengembalikan 7.
