VLOOKUP vs XLOOKUP: bedanya, kapan memakai yang mana, dan INDEX-MATCH untuk Excel versi lama

VLOOKUP dan XLOOKUP menjawab pertanyaan yang sama: untuk kode ini, berapa harganya, siapa namanya, di cabang mana. Bedanya ada pada hal-hal yang baru terasa ketika sesuatu salah: tabel master disisipi kolom, kode tidak ditemukan, atau berkas dibuka di komputer lain. Penilaian redaksi di artikel ini berdasarkan sumber yang dicantumkan di bagian akhir.

Rumus yang sama dengan dua fungsi

' Tabel master Barang di H2:J200: Kode | Nama | Harga
=VLOOKUP(A2, $H$2:$J$200, 3, FALSE)
=XLOOKUP(A2, $H$2:$H$200, $J$2:$J$200)

VLOOKUP menerima satu tabel dan nomor kolom yang diambil, dihitung dari kolom paling kiri tabel itu. XLOOKUP menerima dua rentang terpisah: kolom tempat mencari dan kolom yang diambil. Perbedaan kecil di penulisan ini yang menjadi sumber hampir semua perbedaan lain.

Lima perbedaan yang memengaruhi hasil

Jebakan terbesar VLOOKUP: argumen keempat yang dikosongkan

Kalau argumen range_lookup dikosongkan, VLOOKUP mencari kecocokan perkiraan, yang mengandaikan kolom pertama tabel terurut naik. Pada tabel master kode barang yang tidak terurut, hasilnya bisa harga barang lain tanpa pesan error apa pun. Itu lebih berbahaya daripada #N/A, karena tidak ada yang curiga. Aturan kerjanya satu: selalu tulis FALSE, kecuali kamu memang sedang mencari rentang, misalnya tabel tarif bertingkat.

Kolom yang disisipkan di tabel master

Misalkan ada yang menyisipkan kolom Satuan di antara Nama dan Harga. Rumus VLOOKUP dengan nomor kolom 3 sekarang mengambil Satuan, bukan Harga, dan tetap tidak memberi pesan error. XLOOKUP merujuk kolom Harga langsung sebagai rentang, jadi ikut bergeser bersama kolomnya. Kalau tabel master sering diubah orang lain, ini alasan terkuat untuk memakai XLOOKUP, atau untuk mengubah tabel master menjadi Tabel Excel dan merujuk kolomnya dengan nama.

Kode tidak ditemukan

=IFNA(VLOOKUP(A2, $H$2:$J$200, 3, FALSE), "Tidak ada")
=XLOOKUP(A2, $H$2:$H$200, $J$2:$J$200, "Tidak ada")

Sebelum menutupi #N/A, periksa dulu kenapa kodenya tidak ditemukan. Penyebab yang paling sering bukan kodenya yang hilang, melainkan kodenya terlihat sama tetapi berbeda: spasi di akhir, angka yang tersimpan sebagai teks di satu tabel dan sebagai angka di tabel lain. IFNA lebih aman daripada IFERROR untuk pencarian, karena IFERROR juga menyembunyikan kesalahan rumus yang seharusnya diperbaiki.

Berkas yang dibuka di Excel versi lama

XLOOKUP hanya ada di Excel 2021 ke atas dan Microsoft 365. Kalau berkasmu dikirim ke kantor cabang atau klien yang memakai Excel 2016 atau 2019, rumus XLOOKUP tidak bisa dihitung di sana. Dalam keadaan itu ada dua pilihan yang sama-sama aman: VLOOKUP dengan FALSE dan tabel master yang tidak diubah strukturnya, atau INDEX-MATCH yang bekerja di semua versi dan tetap benar ketika kolom disisipkan.

=INDEX($J$2:$J$200, MATCH(A2, $H$2:$H$200, 0))

Angka 0 di MATCH berarti pencocokan persis, perannya sama dengan FALSE di VLOOKUP. Seperti XLOOKUP, INDEX-MATCH bisa mengambil kolom di kiri kolom pencarian.

Keputusan singkatnya

• Semua orang yang membuka berkas memakai Excel 2021 atau Microsoft 365: pakai XLOOKUP.
• Ada penerima berkas yang memakai versi lama: pakai INDEX-MATCH, atau VLOOKUP dengan FALSE.
• Mencari rentang, seperti tarif ongkir bertingkat: VLOOKUP pencocokan perkiraan dengan tabel terurut, atau XLOOKUP dengan match_mode.
• Rumus lama yang sudah benar dan tabel masternya stabil: tidak perlu diganti hanya karena ada fungsi yang lebih baru.

Ketiga cara ini, termasuk misi dengan kode yang terlihat sama tetapi tidak cocok, dilatih berurutan di kelas Mencari Data: VLOOKUP, XLOOKUP, INDEX-MATCH, dengan lembar kerja di browser yang memeriksa rumus dan hasilnya.