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, jika dibuat oleh orang lain menggunakan versi Excel yang lebih baru.

Browser Anda tidak mendukung video. Instal Microsoft Silverlight, Adobe Flash Player, atau Internet Explorer 9.

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 Jika dihilangkan, XLOOKUP mengembalikan sel kosong yang ditemukan di lookup_array*.
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.

Argumen

Deskripsi

lookup_value

Diperlukan*

Nilai yang akan dicari *Jika dihilangkan, XLOOKUP mengembalikan sel kosong yang ditemukan di lookup_array.

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 fungsi XLOOKUP yang digunakan untuk mengembalikan Nama Karyawan dan Departemen berdasarkan IDt Karyawan. Rumusnya adalah: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

———————————————————————————

Contoh 3 menambahkan argumen if_not_found ke contoh sebelumnya.

Contoh fungsi XLOOKUP yang digunakan untuk mengembalikan Nama Karyawan dan Departemen berdasarkan ID Karyawan dengan argumen if_not_found. Rumusnya adalah =XLOOKUP(B2,B5:B14,C5:D14,0,1,"Karyawan tidak ditemukan")

———————————————————————————

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.

Gambar fungsi XLOOKUP yang digunakan untuk mengembalikan tarif pajak berdasarkan pendapatan maksimum. Ini adalah pertandingan yang mendekati. Rumusnya adalah: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

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 .

Gambar fungsi XLOOKUP yang digunakan untuk mengembalikan data horizontal dari tabel dengan menumpuk 2 XLOOKUP. Rumusnya adalah: =XLOOKUP(D2,$B 6:$B 17,XLOOKUP($C 3,$C 5:$G 5,$C 6:$G 17))

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.

Menggunakan XLOOKUP dengan SUM untuk menjumlahkan rentang nilai yang berada di antara dua pilihan

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.

Fungsi XMATCH

Fungsi Excel (menurut abjad)

Fungsi Excel (menurut kategori)

Sintaks

Contoh

Lihat juga

Perlu bantuan lainnya?

Ingin opsi lainnya?

Apakah informasi ini berguna?

Terima kasih atas umpan balik Anda!