Daftar Isi
×
Rumus & fungsi
Rumus & fungsi

XLOOKUP

Gunakan fungsi XLOOKUP untuk menemukan berbagai hal dalam tabel atau rentang menurut baris. Misalnya, mencari harga komponen otomotif menurut nomor komponennya, atau mencari nama karyawan berdasarkan ID karyawan mereka. Dengan XLOOKUP, Anda dapat mencari di satu kolom untuk istilah pencarian, dan mengembalikan hasil dari baris yang sama di kolom lainnya, terlepas di sisi mana kolom yang dikembalikan berada.

Browser Anda tidak mendukung video.

Sintaks

Fungsi XLOOKUP mencari rentang atau array, lalu mengembalikan item yang terkait dengan kecocokan pertama yang ditemukannya. Jika tidak ada kecocokan, maka XLOOKUP dapat mengembalikan kecocokan terdekat (perkiraan). 

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

Argumen

Deskripsi

lookup_value

Wajib*

Nilai yang dicari

*Jika dihilangkan, XLOOKUP mengembalikan sel kosong yang ditemukannya di lookup_array.   

lookup_array

Diperlukan

Array atau rentang yang ingin dicari

return_array

Diperlukan

Array atau rentang yang ingin dikembalikan

[if_not_found]

Opsional

Jika kecocokan yang valid tidak ditemukan, kembalikan teks [if_not_found] yang Anda paskan.

Jika kecocokan yang valid tidak ditemukan, dan [if_not_found] tidak ditemukan, #N/A akan dikembalikan.

[match_mode]

Opsional

Tentukan tipe yang cocok:

0 - Sama persis. Jika tidak ada yang ditemukan, #N/A. Ini adalah pengaturan default.

-1 - Sama persis. Jika tidak ada yang ditemukan, kembalikan item berikutnya yang lebih kecil.

1 - Sama persis. Jika tidak ada yang ditemukan, kembalikan item berikutnya yang lebih besar.

2 - Wildcard yang cocok dengan tempat *, ?, dan ~ memiliki makna khusus.

[search_mode]

Opsional

Menentukan mode pencarian untuk digunakan:

1 - Melakukan pencarian yang dimulai pada item pertama. Ini adalah pengaturan default.

-1 - Melakukan pencarian terbalik dimulai dari item terakhir.

2 - Melakukan pencarian biner yang mengandalkan lookup_array yang diurutkan dalam urutan naik. Jika tidak diurutkan, hasil yang tidak valid akan dikembalikan.

-2 - Melakukan pencarian biner yang mengandalkan lookup_array yang 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). Fungsi ini tidak menyertakan argumen match_mode, karena XLOOKUP menghasilkan kecocokan persis secara default.

Contoh fungsi XLOOKUP digunakan untuk mengembalikan Nama Karyawan dan Departemen berdasarkan ID Karyawan. Rumusnya adalah =XLOOKUP(B2,B5:B14,C5:C14).

Catatan: XLOOKUP menggunakan array pencarian dan array pengembalian, sedangkan VLOOKUP menggunakan array tabel tunggal diikuti dengan nomor 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 bisa mengembalikan array dengan beberapa item, jadi satu rumus bisa mengembalikan nama karyawan dan departemen dari sel C5:D14.

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

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

Contoh 3    menambahkan if_not_found argumen ke contoh sebelumnya.

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

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

Contoh 4    mencari di kolom C untuk pendapatan pribadi yang dimasukkan di sel E2, dan menemukan tarif pajak yang cocok di kolom B. Fungsi ini mengatur if_not_found angka untuk mengembalikan 0 (nol) jika tidak ditemukan. Argumen match_mode diatur ke 1,yang berarti fungsi akan mencari hasil yang persis sama, dan jika tidak dapat menemukannya, fungsi akan mengembalikan item yang lebih besar berikutnya. Terakhir, argumen search_mode diatur ke 1,yang berarti fungsi akan mencari dari item pertama hingga ke yang terakhir.

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

Catatan: Kolom lookup_array XARRAY berada di sebelah kanan kolom return_array, sementara VLOOKUP hanya dapat melihat dari kiri ke kanan.

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

Contoh 5    menggunakan fungsi XLOOKUP bertumpuk untuk melakukan kecocokan vertikal dan horizontal. Excel terlebih dahulu mencari Laba Kotor di kolom B, lalu mencari Qtr1 di baris atas dari tabel (rentang C5:F5), dan akhirnya mengembalikan nilai di irisan keduanya. Ini sama dengan menggunakan fungsi INDEX danMATCH bersama-sama.

Tips: Anda juga bisa menggunakan XLOOKUP untuk mengganti fungsi HLOOKUP.

Gambar fungsi XLOOKUP 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 di 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, dandua fungsi XLOOKUP bertumpuk, untuk menjumlahkan semua nilai di antara dua rentang. Dalam hal ini, kami ingin menjumlahkan nilai untuk pisang, pisang, dan menyertakan pir, yang berada di antara keduanya.

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

Rumus di sel E3 adalah: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

Bagaimana cara kerjanya? XLOOKUP mengembalikan rentang, jadi saat menghitung, rumus akhirnya terlihat seperti ini: =SUM($E$7:$E$9). Anda bisa melihat cara kerjanya sendiri dengan memilih sel dengan rumus XLOOKUP seperti ini, lalu pilih Rumus > Pengauditan Rumus > Evaluasi Rumus,lalu pilih Evaluasi untuk masuk ke dalam perhitungan.

Catatan: Terima kasih Microsoft Excel MVP, Bill Jelen, untuk menyarankan contoh ini.

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

Perlu bantuan lainnya?

Anda dapat bertanya kapan saja kepada pakar di Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas Jawaban.

Lihat Juga

Fungsi XMATCH

Fungsi Excel (menurut abjad)

Fungsi Excel (menurut kategori)

Perlu bantuan lainnya?

Kembangkan keterampilan Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung Microsoft Insider

Apakah informasi ini bermanfaat?

Seberapa puaskah Anda dengan kualitas terjemahannya?

Apa yang memengaruhi pengalaman Anda?

Ada umpan balik tambahan? (Opsional)

Terima kasih atas umpan balik Anda!

×