Rumus Umum
{=INDEX (data, MATCH (MIN(ABS(data-value)), ABS(data-value),0))}Penjelasan
Untuk menemukan kecocokan terdekat dengan menunjuk pada nilai dan data angka bisa menggunakan susunan rumus berdasarkan INDEX, MATCH,ABS dan MIN. Contoh dibawah ini menggunakan rumus pada E5:
{=INDEX (data, MATCH (MIN(ABS(data-E4)), ABS(data-E4),0))}Dimana “data” adalah jarak B5:B14 dan E4 berisi pencarian nilai.
Penting: ini adalah susuan rumus dan harus diisi dengan control + shift +enter.
Cara rumus bekerja
Pada intinya, Rumus INDEX dan MATCH dimana MATCH menentukan posisi dari kecocokan terdekat dan memposisikan kedalam INDEX. Lalu INDEX akan mengambalikan nilai pada posisi tersebut. Semua kerja keras selesai dalam fungsi MATCH, dimana dapat dilihat sebagai berikut:
MATCH(MIN(ABS(data – E4)), ABS(data–E4),0)Didalam MATCH, penjelasan ini menghitung perbedaan antara pencarian nilai dalam E4 dan nilai dalam jarak data:
Data-E4
Ini adalah urutan data dan kembali menjadi hasil berurutan seperti ini:
{-18;-6 ;-2;1;6;8;10;11;13;19}ABS digunakan untuk merubah nilai negative ke positif:
{18;6;2;1;6;8;10;11;13;19}Nilai-nilai ini mewakili perbedaan antara pencarian nilai dan nilai data. Saat kita mecara kecocokan terdekat, kita menggunakan fungsi MIN untuk mengambalikan nilai terkecil. Dalam kasus ini, nilai terendah adalah 1 dan ini menjadi nilai yang didapat dalam MATCH.
Urutan pencarian dihitung dalam cara yang mirip seperti berikut:
ABS(data–E4)Susunan tersebut kembali pada MATCH seperti susunan pencarian:
{18;6;2;1;6;8;10;11;13;19}Argument terakhir dalam MATCH ada kecocokan tipe, dimana dikondisikan menjadi nol untuk mendapat kecocokan yang yang tepat.
Akhirnya, dengan nilai ini, fungsi MATCH kembali pada posisi 1 dalam urutan, yang merupakan 4. Posisi ini di tempatkan kedalam INDEX sabagai baris penjelasan:
=INDEX(data, 4)Fungsi INDEX lalu mengembalikan nilai pada posisi tersebut, pada 26 Juli 2018.
Catatan: dalam kondisi hubungan, rumus ini akan kembali pada kecocokan pertama