2 Cara Mencari Nilai Terdekat di Excel dengan Rumus XLOOKUP dan INDEX MATCH

Microsoft Excel menyediakan berbagai fungsi yang dapat membantu dalam analisis data, termasuk mencari nilai terdekat dalam suatu dataset. Kemampuan ini sangat berguna ketika kita perlu menemukan angka yang paling mendekati nilai referensi dalam kumpulan data yang besar. Dengan menggunakan rumus yang tepat, kita dapat menghemat waktu dan meningkatkan akurasi dalam mengolah informasi numerik.

Salah satu metode untuk mencari nilai terdekat di Excel adalah dengan menggunakan fungsi XLOOKUP. Fungsi ini memungkinkan pencarian nilai secara fleksibel, baik dalam urutan naik maupun turun, serta mendukung pencocokan nilai yang paling mendekati jika tidak ditemukan kecocokan yang persis. Dengan keunggulan ini, XLOOKUP menjadi pilihan utama bagi banyak pengguna yang ingin menyederhanakan proses pencarian dalam analisis data.

Selain itu, kombinasi fungsi INDEX dan MATCH juga sering digunakan untuk mencari nilai terdekat di Excel, terutama dalam versi Excel yang belum mendukung XLOOKUP. Dengan MATCH, kita dapat menemukan posisi nilai yang paling mendekati dalam suatu daftar, kemudian menggunakan INDEX untuk mengambil nilai dari posisi tersebut. Teknik ini memberikan solusi yang efektif dalam berbagai skenario, seperti menentukan harga terdekat, mencari skor ujian yang paling mendekati, atau mengidentifikasi data dalam rentang tertentu.

Menggunakan Fungsi XLOOKUP untuk Mencari Nilai Terdekat di Excel

Sebagai contoh, misalnya kita memiliki dataset seperti berikut:

Dari tabel di atas, misalnya kita ingin mengambil data transaksi dengan omset Rp 2.000.000 dengan toleransi melesetnya sebesar 50.000. Di sini kita bisa menggunakan fungsi XLOOKUP. Fungsi XLOOKUP merupakan alternatif modern dari VLOOKUP yang memungkinkan pencarian lebih fleksibel. Untuk mencari nilai terdekat di Excel dengan toleransi tertentu (n), kita bisa menggunakan rumus berikut:

=XLOOKUP(1;--(ABS($D$3:$D$17-$F$3)<=$G$3);B$3:B$17)

Dalam rumus ini:

• $D$3:$D$17 adalah kolom yang berisi angka yang ingin kita cari.
• $F$3 adalah nilai referensi yang ingin kita cocokkan.
• $G$3 adalah nilai toleransi (n) yang diizinkan.
• --(ABS($D$3:$D$17-$F$3)<=$G$3) mengubah nilai TRUE/FALSE menjadi angka 1 dan 0 agar cocok dengan XLOOKUP.
• B$3:B$17 adalah rentang data yang akan dikembalikan.

Dengan menggunakan metode ini, kita dapat mencari nilai terdekat di Excel dengan lebih cepat dan efisien. Berikut adalah hasilnya:

Memahami Proses Pencarian dengan XLOOKUP

Untuk memahami bagaimana rumus ini bekerja, mari kita lihat langkah-langkahnya:

1. Fungsi ABS($D$3:$D$17-$F$3) menghitung selisih absolut antara angka dalam dataset dan nilai referensi.
2. Hasil perhitungan ini dibandingkan dengan nilai toleransi di G3.
3. Hasil perbandingan akan menghasilkan array TRUE dan FALSE.
4. -- mengubah array TRUE dan FALSE menjadi 1 dan 0.
5. XLOOKUP(1; array; B$3:B$17) kemudian mencari nilai 1 pertama dalam array dan mengembalikan data yang sesuai.

Metode ini sangat efektif untuk mencari nilai terdekat di Excel, terutama ketika bekerja dengan dataset yang besar.

Menggunakan INDEX dan MATCH untuk Mencari Nilai Terdekat di Excel

Alternatif lain untuk mencari nilai terdekat di Excel adalah dengan kombinasi INDEX dan MATCH:

=INDEX(B$3:B$17;MATCH(1;--(ABS($D$3:$D$17-$F$3)<=$G$3);0);0)

Dalam rumus ini:

• MATCH(1;--(ABS($D$3:$D$17-$F$3)<=$G$3);0) mencari posisi pertama dalam array yang memenuhi kriteria.
• INDEX(B$3:B$17; row_num; 0) mengembalikan seluruh baris dari dataset berdasarkan hasil MATCH.

Berikut hasiilnya dalam tabel:

Keuntungan Menggunakan XLOOKUP dan INDEX-MATCH

1. Fleksibilitas: Baik XLOOKUP maupun INDEX-MATCH memungkinkan pencarian nilai yang lebih kompleks dibandingkan VLOOKUP.
2. Kinerja Lebih Baik: XLOOKUP lebih efisien dalam menangani pencarian tanpa perlu mengurutkan data terlebih dahulu.
3. Dapat Mengembalikan Seluruh Baris: Dengan INDEX-MATCH, kita dapat mengembalikan seluruh baris data terkait, bukan hanya satu kolom.

Kesimpulan

Mencari nilai terdekat di Excel bisa dilakukan dengan berbagai metode. XLOOKUP menawarkan solusi modern dengan sintaks yang lebih sederhana, sedangkan kombinasi INDEX dan MATCH memberikan fleksibilitas dalam berbagai situasi. Dengan memahami cara kerja kedua metode ini, Anda dapat memilih teknik terbaik sesuai dengan kebutuhan analisis data Anda.

Dengan menggunakan salah satu metode di atas, Anda dapat dengan mudah mencari nilai terdekat di Excel, memastikan analisis data lebih akurat dan efisien.

Download Template Gratis

Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempaktikkan 2 Cara Mencari Nilai Terdekat di Excel dengan Rumus XLOOKUP dan INDEX MATCH ini. File Excel bisa didownload melalui tautan berikut:

Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!