Formula generik
=INDEX(location,XMATCH(0,distance,1))
Ringkasan
Untuk mencari lokasi terdekat dengan jarak jauh, anda boleh menggunakan formula berdasarkan fungsi XMATCH dengan fungsi INDEX. Dalam contoh yang ditunjukkan, formula dalam sel E5 adalah:
=INDEX(location,XMATCH(0,distance,1))
di mana lokasi (B5: B12) dan jarak (C5: C12) dinamakan julat.
Penjelasan
Pada intinya, formula ini adalah formula INDEX dan MATCH asas. Namun, bukannya menggunakan fungsi MATCH yang lebih lama, kami menggunakan fungsi XMATCH, yang menyediakan tetapan mod padanan yang lebih kuat:
=INDEX(location,XMATCH(0,distance,1))
Bekerja dari dalam ke luar, kami menggunakan fungsi XMATCH untuk mencari kedudukan lokasi terdekat:
XMATCH(0,distance,1) // find row nearest zero
Kami melakukannya dengan menetapkan nilai carian ke sifar (0), tatasusunan mencari ke jarak (C5: C12), dan mod padan ke 1.
Nilai mod padanan 1 memberitahu XMATCH untuk mencari padanan tepat atau nilai terbesar seterusnya. Oleh kerana nilai carian diberikan sebagai sifar (0), XMATCH akan mendapati jarak pertama lebih besar daripada sifar. Manfaat yang baik dari XMATCH - yang membezakannya daripada PERTANDINGAN - adakah array carian tidak disusun. Tanpa mengira pesanan, MATCH akan mengembalikan padanan tepat pertama atau nilai terbesar seterusnya.
Dalam contohnya, XMATCH mengembalikan 5, kerana jarak terkecil adalah 7 (lokasi G), yang muncul kelima dalam senarai. Rumus memutuskan untuk:
=INDEX(location,5) // returns "G"
dan INDEX mengembalikan item kelima dari lokasi julat bernama (B5: B12), iaitu "G".
Catatan: walaupun seri, XMATCH akan mengembalikan perlawanan pertama untuk nilai seri.
Dapatkan jarak
Rumus untuk mengembalikan jarak sebenar lokasi terdekat hampir sama. Daripada memberikan nama lokasi kepada INDEX, kami memberikan jarak kepada INDEX. Formula dalam F5 adalah:
=INDEX(distance,XMATCH(0,distance,1)) // returns distance
XMATCH mengembalikan hasil yang sama seperti di atas (5), dan INDEX mengembalikan 7.
