Fungsi XLOOKUP

Gunakan fungsi Xlookup saat Anda perlu menemukan berbagai hal dalam tabel atau rentang menurut baris. Sebagai contoh, Cari harga bagian otomotif dengan nomor komponen, atau temukan nama karyawan berdasarkan ID karyawannya. Dengan XLOOKUP, Anda bisa mencari dalam satu kolom untuk istilah pencarian, dan mengembalikan hasil dari baris yang sama di kolom lain, apa pun sisi yang dikembalikan.

Membuat dampak besar dengan Pengumuman

Catatan: Fungsi ini saat ini tersedia untuk Microsoft 365 pelanggan di saluran bulanan. Ini akan tersedia untuk Microsoft 365 pelanggan di saluran semi tahunan yang dimulai pada Juli 2020. Untuk informasi selengkapnya tentang cara fitur diluncurkan kepada pelanggan Microsoft 365 , lihat Kapan saya mendapatkan fitur terbaru untuk Microsoft 365.

Fungsi XLOOKUP mencari sebuah rentang atau array, dan mengembalikan item yang berhubungan dengan kecocokan pertama. Jika kecocokan tidak ada, 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

Diperlukan

Nilai pencarian

lookup_array

Diperlukan

Larik atau rentang untuk pencarian

return_array

Diperlukan

Larik atau rentang untuk kembali

[if_not_found]

Opsional

Di mana kecocokan yang valid tidak ditemukan, Kembalikan teks [if_not_found] yang Anda suplai.

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

[match_mode]

Opsional

Menentukan tipe kecocokan:

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

-pencocokan persis 1. Jika tidak ditemukan, Kembalikan item yang lebih kecil berikutnya.

1-pencocokan persis. Jika tidak ditemukan, Kembalikan item yang lebih besar berikutnya.

2-pertandingan wildcard di mana *,?, dan ~ memiliki arti khusus.

[search_mode]

Opsional

Tentukan mode pencarian untuk digunakan:

1-melakukan pencarian dimulai dari item pertama. Ini adalah pengaturan default.

-1-melakukan pencarian terbalik dimulai dari item terakhir.

2-lakukan 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 bergantung pada lookup_array diurutkan dalam urutan menurun . Jika tidak diurutkan, hasil yang tidak valid akan dikembalikan.

Contoh

Contoh 1

Contoh ini berasal dari video di atas, dan menggunakan XLOOKUP sederhana untuk mencari nama negara, lalu mengembalikan kode negara teleponnya. Hanya menyertakan lookup_value (sel F2), lookup_array (rentang B2: B11), dan return_array (rentang D2: D11). Tidak menyertakan argumen match_mode, sebagai XLOOKUP default ke kecocokan yang sama persis.

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

Catatan: XLOOKUP berbeda dengan VLOOKUP yang menggunakan array lookup dan Return yang terpisah, di mana VLOOKUP menggunakan array tabel tunggal yang diikuti dengan nomor indeks kolom. Rumus VLOOKUP yang setara dalam kasus ini adalah: = VLOOKUP (F2, B2: D11,3, FALSE)

Contoh 2

Dalam contoh ini, kami mencari informasi karyawan berdasarkan nomor ID karyawan. Tidak seperti VLOOKUP, XLOOKUP dapat mengembalikan array dengan beberapa item, yang memperbolehkan satu rumus untuk 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

Contoh ini menambahkan argumen if_not_found ke contoh di atas.

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

Contoh berikut ini terlihat di kolom C untuk pendapatan pribadi yang dimasukkan di sel E2, dan menemukan tarif pajak yang cocok di kolom B. Ini mengatur argumen if-not_found untuk mengembalikan 0 jika tidak ditemukan. Argumen match_mode diatur ke 1, yang berarti fungsi akan mencari kecocokan yang tepat, dan jika tidak bisa menemukannya, maka akan mengembalikan item yang lebih besar berikutnya. Akhirnya, argumen search_mode diatur ke 1, yang berarti fungsi akan mencari dari item pertama hingga yang terakhir.

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

Catatan: Tidak seperti VLOOKUP, kolom lookup_array berada di sebelah kanan kolom return_array, di mana VLOOKUP hanya bisa terlihat dari kiri-ke-kanan.

Contoh 5

Berikutnya, kita akan menggunakan fungsi XLOOKUP bertumpuk untuk melakukan kecocokan vertikal dan horizontal. Dalam kasus ini, pertama-tama akan mencari laba kotor di kolom B, lalu Cari Qtr1 di baris atas tabel (rentang C5: F5), dan kembalikan nilai di irisan dua. Ini mirip dengan menggunakan fungsi index dan Match dalam kaitannya. Anda juga bisa menggunakan XLOOKUP untuk mengganti fungsi hlookup .

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

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

Contoh ini menggunakan fungsi Sum, dan dua fungsi xlookup bertumpuk untuk menjumlahkan semua nilai di antara dua rentang. Dalam kasus ini, kami ingin menjumlahkan nilai untuk buah anggur, pisang, dan menyertakan pir, yang berada di antara keduanya.

Menggunakan XLOOKUP dengan SUM untuk total 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 sebuah rentang, sehingga ketika menghitung rumus berakhir terlihat seperti ini: = SUM ($E $7: $E $9). Anda dapat melihat cara kerjanya pada Anda sendiri dengan memilih sel dengan rumus XLOOKUP yang sama dengan rumus ini, lalu masuk ke rumus > pengauditan rumus > mengevaluasi rumus, dan tekan tombol evaluasi untuk menelusuri perhitungan.

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

Perlu bantuan lainnya?

Anda dapat bertanya kapan saja kepada para ahli di Komunitas Teknologi Excel, mendapatkan dukungan di Komunitas Answers, atau menyarankan peningkatan maupun fitur baru di User Voice Excel.

Lihat Juga

Fungsi XMATCH

Fungsi Excel (menurut abjad)

Fungsi Excel (menurut kategori)

Catatan:  Halaman ini diterjemahkan menggunakan mesin dan mungkin terdapat kesalahan tata bahasa atau masalah keakuratan. Kami bertujuan menyediakan konten yang bermanfaat untuk Anda. Dapatkah Anda memberi tahu kami apakah informasi ini bermanfaat untuk Anda? Berikut adalah artikel dalam bahasa Inggris untuk referensi.

Kembangkan keterampilan Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

Terima kasih atas umpan balik Anda!

Terima kasih atas umpan balik Anda! Sepertinya menghubungkan Anda ke salah satu agen dukungan Office kami akan sangat membantu.

×