XLOOKUP di Excel, langkah demi langkah
XLOOKUP mencari sebuah nilai di satu kolom dan mengembalikan nilai dari baris yang sama di kolom lain. Rumus ini dipakai untuk membawa alamat email ke daftar ID karyawan, harga ke lembar pesanan, atau ongkos kirim ke daftar paket. Fungsi XLOOKUP tersedia di Microsoft 365, Excel 2021 dan versi sesudahnya, serta Excel untuk web. Di Excel 2019 atau versi lebih lama, fungsi ini belum ada dan rumusnya menghasilkan #NAME?.
=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])
lookup_valueadalah nilai yang dicari, biasanya sel sepertiF2.lookup_arrayadalah satu kolom tempat nilai itu dicari.return_arrayadalah kolom asal jawaban. Jumlah barisnya harus sama denganlookup_array.if_not_foundadalah isi yang ditampilkan jika tidak ada yang cocok. Tanpa argumen ini, hasilnya#N/A.match_modeberarti pencocokan eksak jika dikosongkan. Untuk tabel rentang, seperti ongkos kirim, pakai-1: cocok eksak, atau jika tidak ada, nilai berikutnya yang lebih kecil.search_modemencari dari atas ke bawah jika dikosongkan.
Contoh
Daftar karyawan ada di A1:D5, dan Anda perlu alamat email untuk ID di F2:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID | Nama | Divisi | |
| 2 | E-1021 | Rina Wulandari | Keuangan | [email protected] |
| 3 | E-1034 | Budi Santoso | Penjualan | [email protected] |
| 4 | E-1047 | Sari Putri | SDM | [email protected] |
| 5 | E-1052 | Andi Pratama | Penjualan | [email protected] |
=XLOOKUP(F2;$A$2:$A$5;$D$2:$D$5)
Jika F2 berisi E-1047, XLOOKUP menemukan ID itu di kolom A dan mengembalikan email dari kolom D di baris yang sama: [email protected]. Anda tidak menghitung nomor kolom, cukup menunjuk kolom yang diinginkan. Karena itu, jika ada kolom baru yang disisipkan ke tabel, rumusnya tetap mengembalikan kolom yang benar.
Mencari ke kiri
lookup_array dan return_array adalah dua rentang yang terpisah, jadi jawabannya boleh berada di sebelah kiri kolom yang dicari. Untuk mencari ID dari nama: =XLOOKUP(F3;$B$2:$B$5;$A$2:$A$5). VLOOKUP hanya mencari di kolom pertama tabel, sehingga dulu pencarian ini butuh gabungan INDEX dan MATCH.
Data yang tidak ditemukan
Argumen keempat mengganti #N/A dengan teks yang mudah dibaca, tanpa membungkus rumus dengan IFNA:
=XLOOKUP(F2;$A$2:$A$5;$D$2:$D$5;"Tidak ada di daftar")
#N/A yang dibiarkan akan merusak total dan setiap rumus yang membaca sel itu. Jika nilai yang seharusnya ada tidak ditemukan, periksa spasi di akhir teks dan angka yang tersimpan sebagai teks di salah satu sisi.
Tabel rentang dan pencocokan perkiraan
Tabel ongkos kirim berdasarkan berat tidak punya baris untuk setiap berat yang tepat. Dengan -1 sebagai match_mode, XLOOKUP mengembalikan baris nilai berikutnya yang lebih kecil jika tidak ada yang cocok eksak:
| H | I | |
|---|---|---|
| 1 | Mulai (kg) | Ongkos |
| 2 | 0 | 6,00 |
| 3 | 2 | 9,00 |
| 4 | 5 | 14,00 |
| 5 | 10 | 21,00 |
=XLOOKUP(K2;$H$2:$H$5;$I$2:$I$5;;-1)
Jika K2 berisi 6,5, rumus mengembalikan 14,00, yaitu ongkos untuk berat mulai 5 kg. Kosongkan argumen if_not_found, tetapi biarkan titik komanya. Berbeda dengan pencocokan perkiraan pada VLOOKUP, cara ini tetap berjalan walaupun tabel rentangnya tidak berurutan. Mencari dari bawah untuk kecocokan terakhir dan memakai wildcard dilatih di lesson XLOOKUP lanjutan.
Latihan di lesson ini dikerjakan di spreadsheet yang bekerja seperti Excel. Ketik rumusnya, tekan Enter, dan hasilnya diperiksa di sel, jadi rumus apa pun yang benar akan diterima.