Rumus & fungsi

XLOOKUP

Fungsi XLOOKUP

Gunakan fungsi Xlookup untuk menemukan berbagai hal di 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.

Browser Anda tidak mendukung video.

Sintaks

Fungsi XLOOKUP mencari rentang atau array, lalu mengembalikan item yang terkait dengan kecocokan pertama yang ditemukan. 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

Diharuskan

Nilai untuk mencari

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

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] hilang, #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    menggunakan xlookup untuk mencari nama negara dalam sebuah rentang, lalu mengembalikan kode negara teleponnya. Ini mencakup argumen lookup_value (sel F2), Lookup_array (rentang B2: B11), dan Return_array (rentang D2: D11). Tidak menyertakan argumen match_mode , karena xlookup menghasilkan kecocokan persis secara default.

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

Catatan: XLOOKUP menggunakan array lookup dan array return, sedangkan 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    mencari informasi karyawan berdasarkan nomor id karyawan. Tidak seperti VLOOKUP, XLOOKUP dapat mengembalikan array dengan beberapa item, sehingga satu rumus bisa 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 di sel E2, dan menemukan tarif pajak yang cocok di kolom B. Ini mengatur argumen if_not_found untuk mengembalikan 0 (nol) 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: Kolom lookup_array xarray berada di sebelah kanan kolom RETURN_ARRAY , sedangkan Vlookup hanya bisa terlihat dari kiri ke kanan.

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

Contoh 5    menggunakan fungsi XLOOKUP bertumpuk untuk melakukan kecocokan vertikal dan horizontal. Pertama kali mencari laba kotor di kolom B, lalu Cari Qtr1 di baris atas tabel (rentang C5: F5), dan akhirnya mengembalikan nilai di irisan dua. Ini mirip dengan menggunakan fungsi index dan Match bersamaan.

Tips: 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))

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, 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 sendiri dengan memilih sel dengan rumus XLOOKUP yang sama dengan rumus ini, lalu pilih rumus > pengauditan rumus > mengevaluasi rumus, lalu pilih 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 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 Office Anda
Jelajahi pelatihan
Dapatkan fitur baru terlebih dahulu
Gabung ke Office Insiders

Apakah informasi ini bermanfaat?

×