Formula umum
(=INDEX(data,MATCH(MIN(ABS(data-value)),ABS(data-value),0)))
Ringkasan
Untuk menemukan kecocokan terdekat dalam data numerik, Anda dapat menggunakan INDEX dan MATCH, dengan bantuan dari fungsi ABS dan MIN. Dalam contoh yang ditunjukkan, rumus di F5, disalin ke bawah, adalah:
=INDEX(trip,MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0))
di mana perjalanan (B5: B14) dan biaya (C5: C14) diberi nama rentang.
Di F5, F6, dan F7, rumus mengembalikan perjalanan terdekat dengan biaya masing-masing ke 500, 1000, dan 1500.
Catatan: ini adalah rumus array dan harus dimasukkan dengan control + shift + enter, kecuali di Excel 365.
Penjelasan
Pada intinya, ini adalah rumus INDEX dan MATCH: MATCH menempatkan posisi yang paling cocok, memasukkan posisi ke INDEX, dan INDEX mengembalikan nilai pada posisi itu di kolom Perjalanan. Kerja keras dilakukan dengan fungsi MATCH, yang dikonfigurasi secara cermat untuk mencocokkan "perbedaan minimum" seperti ini:
MATCH(MIN(ABS(cost-E5)),ABS(cost-E5),0)
Mengambil langkah demi langkah, nilai pencarian dihitung dengan MIN dan ABS seperti ini:
MIN(ABS(cost-E5)
Pertama, nilai E5 dikurangkan dari biaya rentang bernama (C5: C14). Ini adalah operasi larik, dan karena ada 10 nilai dalam rentang tersebut, hasilnya adalah larik dengan 10 nilai seperti ini:
(899;199;250;-201;495;1000;450;-101;500;795)
Angka-angka ini mewakili selisih antara setiap biaya di C5: C15 dan biaya di sel E5, 700. Beberapa nilai negatif karena biayanya lebih rendah daripada angka di E5. Untuk mengubah nilai negatif menjadi nilai positif, kami menggunakan fungsi ABS:
ABS((899;199;250;-201;495;1000;450;-101;500;795))
yang mengembalikan:
(899;199;250;201;495;1000;450;101;500;795)
Kami mencari kecocokan terdekat, jadi kami menggunakan fungsi MIN untuk menemukan perbedaan terkecil, yaitu 101:
MIN((899;199;250;201;495;1000;450;101;500;795)) // returns 101
Ini menjadi nilai pencarian di dalam MATCH. Array pencarian dibuat seperti sebelumnya:
ABS(cost-E5) // generate lookup array
yang mengembalikan larik yang sama yang kita lihat sebelumnya:
(899;199;250;201;495;1000;450;101;500;795)
Kami sekarang memiliki apa yang kami butuhkan untuk menemukan posisi yang paling cocok (perbedaan terkecil), dan kami dapat menulis ulang bagian MATCH dari rumus seperti ini:
MATCH(101,(899;199;250;201;495;1000;450;101;500;795),0) // returns 8
Dengan 101 sebagai nilai pencarian, MATCH mengembalikan 8, karena 101 berada di posisi ke-8 dalam larik. Terakhir, posisi ini dimasukkan ke dalam INDEX sebagai argumen baris, dengan perjalanan rentang bernama sebagai larik:
=INDEX(trip,8)
dan INDEX mengembalikan perjalanan ke-8 dalam kisaran, "Spanyol". Saat rumus disalin ke sel F6 dan F7, rumus menemukan kecocokan yang paling mendekati 1000 dan 1500, "Prancis" dan "Thailand" seperti yang ditunjukkan.
Catatan: jika ada seri, rumus ini akan mengembalikan pertandingan pertama.
Dengan XLOOKUP
Fungsi XLOOKUP menyediakan cara yang menarik untuk memecahkan masalah ini, karena jenis pencocokan 1 (pencocokan tepat atau terbesar berikutnya) atau -1 (pencocokan tepat atau terkecil berikutnya) tidak memerlukan data untuk diurutkan. Artinya kita bisa menulis rumus seperti ini:
=XLOOKUP(0,ABS(cost-E5),trip,,1)
Seperti di atas, kami menggunakan nilai absolut (cost-E5) untuk membuat array pencarian:
(899;199;250;201;495;1000;450;101;500;795)
Kemudian kami mengonfigurasi XLOOKUP untuk mencari nol, dengan jenis pencocokan disetel ke 1, untuk pencocokan tepat atau terbesar berikutnya. Kami menyediakan perjalanan rentang bernama sebagai larik kembali, sehingga hasilnya adalah "Spanyol" seperti sebelumnya.
