Fungsi XLOOKUP
Gunakan fungsi XLOOKUP untuk menemukan berbagai hal dalam tabel atau rentang menurut baris. Misalnya, cari harga komponen otomotif berdasarkan nomor komponen, atau temukan nama karyawan berdasarkan ID karyawan mereka. Dengan XLOOKUP, Anda bisa mencari dalam satu kolom untuk istilah pencarian dan mengembalikan hasil dari baris yang sama di kolom lain, terlepas dari sisi mana kolom yang dikembalikan berada.
Catatan: XLOOKUP tidak tersedia di Excel 2016 dan Excel 2019, namun, Anda mungkin menemukan situasi penggunaan buku kerja di Excel 2016 atau Excel 2019 dengan fungsi XLOOKUP di dalamnya yang dibuat oleh orang lain menggunakan versi Excel yang lebih baru.
Sintaks
Fungsi XLOOKUP mencari rentang atau array, lalu mengembalikan item yang terkait dengan kecocokan pertama yang ditemukannya. Jika tidak ada kecocokan, XLOOKUP dapat mengembalikan kecocokan terdekat (perkiraan).
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
|
Argumen |
Deskripsi |
|---|---|
|
lookup_value Diperlukan* |
Nilai yang akan dicari |
|
lookup_array Diperlukan |
Array atau rentang untuk dicari |
|
return_array Diperlukan |
Array atau rentang yang akan dikembalikan |
|
[if_not_found] Opsional |
Jika kecocokan valid tidak ditemukan, kembalikan teks [if_not_found] yang Anda masukkan. Jika kecocokan valid tidak ditemukan, dan [if_not_found] hilang, #N/A dikembalikan. |
|
[match_mode] Opsional |
Tentukan tipe yang cocok: 0 - Persis cocok. Jika tidak ditemukan, kembalikan #N/A. Ini adalah pengaturan default. -1 - Persis cocok. Jika tidak ada yang ditemukan, kembalikan item berikutnya yang lebih kecil. 1 - Persis sama. Jika tidak ditemukan, kembalikan item berikutnya yang lebih besar. 2 - A wildcard match where *, ?, and ~ have special meaning. |
|
[search_mode] Opsional |
Tentukan mode pencarian yang akan digunakan: 1 - Melakukan pencarian dimulai dari item pertama. Ini adalah pengaturan default. -1 - Melakukan pencarian terbalik dimulai dari item terakhir. 2 - Melakukan pencarian biner yang bergantung pada lookup_array diurutkan dalam urutan naik . Jika tidak diurutkan, hasil yang tidak valid akan dikembalikan. -2 - Melakukan pencarian biner yang mengandalkan lookup_array diurutkan dalam urutan menurun . Jika tidak diurutkan, hasil yang tidak valid akan dikembalikan. |
Contoh
Contoh 1 menggunakan XLOOKUP untuk mencari nama negara dalam rentang, lalu mengembalikan kode negara teleponnya. Ini mencakup argumen lookup_value (sel F2), lookup_array (rentang B2:B11), dan return_array (rentang D2:D11). Ini tidak menyertakan argumen match_mode , karena XLOOKUP menghasilkan kecocokan persis secara default.
Catatan: XLOOKUP menggunakan array pencarian dan array yang dikembalikan, sedangkan VLOOKUP menggunakan array tabel tunggal diikuti dengan angka indeks kolom. Rumus VLOOKUP yang setara dalam hal ini adalah: =VLOOKUP(F2,B2:D11,3,FALSE)
———————————————————————————
Contoh 2 mencari informasi karyawan berdasarkan nomor ID karyawan. Tidak seperti VLOOKUP, XLOOKUP dapat mengembalikan array dengan beberapa item, sehingga rumus tunggal dapat mengembalikan nama karyawan dan departemen dari sel C5:D14.
———————————————————————————
Contoh 3 menambahkan argumen if_not_found ke contoh sebelumnya.
———————————————————————————
Contoh 4 terlihat di kolom C untuk pendapatan pribadi yang dimasukkan dalam sel E2, dan menemukan tarif pajak yang cocok di kolom B. Ini mengatur argumen if_not_found untuk mengembalikan 0 (nol) jika tidak ada yang ditemukan. Argumen match_mode diatur ke 1, yang berarti fungsi akan mencari kecocokan yang persis, dan jika tidak dapat menemukannya, fungsi mengembalikan item yang lebih besar berikutnya. Akhirnya, argumen search_mode diatur ke 1, yang berarti fungsi akan mencari dari item pertama ke yang terakhir.
Catatan: Kolom lookup_array XARRAY berada di sebelah kanan kolom return_array , sedangkan VLOOKUP hanya dapat terlihat dari kiri ke kanan.
———————————————————————————
Contoh 5 menggunakan fungsi XLOOKUP bertumpuk untuk melakukan kecocokan vertikal dan horizontal. Pertama mencari Laba Kotor di kolom B, lalu mencari Qtr1 di baris atas tabel (rentang C5:F5), dan akhirnya mengembalikan nilai di irisan keduanya. Ini mirip dengan menggunakan fungsi INDEX dan MATCH bersama-sama.
Tips: Anda juga dapat menggunakan XLOOKUP untuk mengganti fungsi HLOOKUP .
Catatan: Rumus dalam sel D3:F3 adalah: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))).
———————————————————————————
Contoh 6 menggunakan fungsi SUM, dan dua fungsi XLOOKUP bertumpuk, untuk menjumlahkan semua nilai di antara dua rentang. Dalam hal ini, kami ingin menjumlahkan nilai untuk anggur, pisang, dan termasuk pir, yang berada di antara keduanya.
Rumus dalam sel E3 adalah: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
Bagaimana cara kerjanya? XLOOKUP mengembalikan rentang, jadi ketika dihitung, rumus akhirnya terlihat seperti ini: =SUM($E$7:$E$9). Anda dapat melihat cara kerjanya sendiri dengan memilih sel dengan rumus XLOOKUP yang mirip dengan sel ini, lalu pilih Rumus > Pengauditan Rumus > Evaluasi Rumus, lalu pilih Evaluasi untuk menelusuri penghitungan.
Catatan: Terima kasih kepada Microsoft Excel MVP, Bill Jelen, untuk menyarankan contoh ini.
———————————————————————————
Lihat juga
Anda selalu dapat bertanya kepada ahli di Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas.
Perlu bantuan lainnya?
Ingin opsi lainnya?
Jelajahi manfaat langganan, telusuri kursus pelatihan, pelajari cara mengamankan perangkat Anda, dan banyak lagi.
Komunitas membantu Anda bertanya dan menjawab pertanyaan, memberikan umpan balik, dan mendengar dari para ahli yang memiliki pengetahuan yang luas.
