Pengertian XLOOKUP di Microsoft Excel: Kupas Tuntas

Apa itu XLOOKUP di Excel?

Fungsi XLOOKUP di Excel sedang dipuji sebagai pengganti VLOOKUP dan HLOOKUP, maka “X” berdiri untuk kedua karakter.

Seperti pendahulunya, XLOOKUP mencari nilai dalam tabel dan mengembalikan hasil yang cocok.

Tetapi sementara fungsi pencarian lainnya memerlukan pasangan kompleks dengan fungsi lain seperti INDEX/MATCH untuk mencari di area yang berbeda, atau IFERROR untuk menampilkan pesan yang disesuaikan, XLOOKUP hadir dengan fungsi bawaan tersebut.

Dan siapa yang ingin mengetik dua rumus, bukan hanya satu?

Pengubah permainan yang besar adalah bahwa rumus VLOOKUP dan HLOOKUP mengasumsikan bahwa Anda akan menerima hampir-cocok, dan bahwa data Anda sudah diurutkan. Sebaliknya, tidak diperlukan penyortiran dengan XLOOKUP, dan pencarian default ke pencocokan tepat.

Untuk saat ini, XLOOKUP hanya tersedia di Excel 365, jadi jika Anda menggunakan versi yang lebih lama, Anda tidak akan bisa mencoba fungsionalitas baru yang mengkilap ini. Tapi alternatif yang bagus adalah tersedia di Excel berbasis browser dengan Office online.



Secara keseluruhan, XLOOKUP di Excel jauh lebih fleksibel. Dengan melihat sintaks, kita dapat melihat beberapa argumen yang tidak digunakan di VLOOKUP atau HLOOKUP. Mari kita hancurkan mereka.

xlookup dalam sintaks excel

Sintaksis

sintaks fungsi xlookup

Definisi argumen

    1. Lookup_value – nilai yang ingin Anda cari
    1. Lookup_array – daftar tempat Anda ingin Excel mencari nilai ini
    1. Return_array – daftar dari mana Anda menginginkan hasilnya
    1. If_not_found – apa yang ditampilkan jika nilai pencarian tidak ada (opsional)
    1. Match_mode – hanya pencocokan persis, atau menerima perkiraan kecocokan (opsional)
    1. Search_mode – pencarian pertama hingga terakhir, atau urutan terbalik (opsional)

Argumen 1: Nilai pencarian

=XLOOKUP(nilai lookuppencarian_array, kembali_array, [if_not_found], [match_mode], [search_mode])

Lookup_value adalah nilai yang Anda inginkan untuk dicari oleh Excel. Tidak ada perubahan di sini.

Argumen 2: Array pencarian

=XLOOKUP(nilai_pencarian, pencarian_array, kembali_array, [if_not_found], [match_mode], [search_mode])

Lookup_array adalah di mana Anda ingin Excel mencari nilai pencarian. Ini sebanding dengan kolom pertama di table_array VLOOKUP, atau baris pertama di table_array HLOOKUP. Perbedaan utama di sini adalah bahwa kami tidak mereferensikan seluruh tabel pada saat ini, hanya rentang yang mungkin berisi nilai yang ingin kami cari.

Baca Juga:  Apa itu Referensi Absolut di Microsoft Excel

Argumen 3: Kembalikan array

=XLOOKUP(nilai_pencarian, array_pencarian, kembali_array, [if_not_found], [match_mode], [search_mode])

Return_array adalah larik atau rentang yang berisi nilai yang ingin Anda tampilkan di Excel sebagai hasilnya. Poin penting yang perlu diperhatikan adalah panjang array pencarian dan pengembalian harus sama.

Misalnya, jika kolom pencarian Anda panjangnya 16 baris, kolom hasil (hasil) Anda juga harus 16 baris sehingga selalu ada baris yang cocok dengan nilai yang ditemukan. Jika tidak, Anda akan mendapatkan kesalahan #VALUE.

Argumen 4: Jika tidak ditemukan

=XLOOKUP(nilai_pencarian, array_pencarian, array_kembali, [if_not_found], [match_mode], [search_mode])

Fitur yang berguna dengan XLOOKUP adalah memungkinkan Anda memberi tahu pengguna, dalam bahasa sehari-hari, bahwa nilai yang mereka cari tidak ada dalam larik pencarian. Sebagai contoh:

=XLOOKUP(B12,A3:A10,G3:G10,“Nilai ini tidak ada”,0)

Jika pengguna mengetikkan nilai di sel B12 yang tidak ditemukan dalam rentang A3:A10, mereka akan melihat respons “Nilai ini tidak ada.” Ingatlah untuk menempatkan teks Anda dalam tanda kutip ganda (“ ”). Argumen ini opsional, tetapi jauh lebih baik bagi pengguna akhir untuk melihat “Nilai ini tidak ada.” Jika tidak ada yang dimasukkan dalam Argumen 4, Anda akan melihat kesalahan “#NA”.

Argumen 5: Mode pencocokan

=XLOOKUP(nilai_pencarian, array_pencarian, array_kembali, [if_not_found], [match_mode], [search_mode])

XLOOKUP mencari kecocokan persis secara default. Ini berarti bahwa jika kita ingin Excel hanya mencari apa yang dimasukkan dalam sel lookup_value, kita bisa mengetikkan 0 di Argumen 5, atau membiarkannya kosong, karena itulah defaultnya. Jika tidak ada kecocokan persis yang ditemukan, Excel akan mengembalikan kesalahan #NA, atau apa pun yang dimasukkan dalam Argumen 4.

Tapi jika kamu melakukan ingin menerima perkiraan kecocokan, Argumen 5 memungkinkan Anda melakukan itu. Opsi yang mungkin untuk Argumen 5 adalah:

0 pencocokan persis saja (default)

-1 pencocokan tepat atau nilai terdekat lebih kecil dari nilai pencarian (perkiraan lebih kecil)

1 pencocokan tepat atau nilai terdekat lebih besar dari nilai pencarian (perkiraan lebih besar)

2 pertandingan wildcard.

Perkiraan pertandingan yang lebih kecil

Katakanlah Anda memberikan insentif staf berdasarkan volume penjualan seperti yang ditunjukkan di bawah ini. Angka-angka di kolom “Volume Penjualan” adalah batas bawah untuk memenuhi syarat untuk jumlah di kolom “Bonus”. Jadi karyawan yang melakukan penjualan 0-100 tidak memenuhi syarat untuk mendapatkan insentif, tetapi karyawan yang melakukan penjualan 301-500 akan mendapatkan $400.

Baca Juga:  6 Fungsi Baru di Excel 2019 dan Office 365 yang Wajib Anda Pelajari

Di sebelahnya ada meja lain yang berisi nama-nama karyawan dan kinerja penjualan masing-masing.

kira-kira pertandingan yang lebih kecilAgar Excel menentukan jumlah bonus untuk setiap karyawan di kolom H, XLOOKUP menawarkan solusi sederhana.

Menggunakan sintaks fungsi XLOOKUP kami, kami tahu bahwa nilai_pencarian kami akan ditemukan di kolom F, susunan_pencarian kami di kolom A, susunan_pengembalian kami di kolom B, dan kami akan menerima perkiraan kecocokan karena angka penjualan karyawan mungkin tidak persis seperti yang ada di kolom A.

Rumus kami terlihat seperti ini:

=XLOOKUP(F2,A2:A5,B2:B5,,-1)

Ini akan memastikan bahwa karyawan seperti Rod Nee akan menerima $200 untuk 166 penjualannya, tetapi Paige Turner tidak akan menerima bonus karena dia hanya menghasilkan 63 penjualan.

Perkiraan pertandingan yang lebih besar

Di lain waktu, kami ingin Excel menemukan nilai terdekat yang lebih besar dari nilai pencarian jika kecocokan persis tidak ditemukan. Dalam contoh ini, kami mengetik 1 di Argumen 5. Misalnya:

=XLOOKUP(D2,A2:A5,B2:B5,,1)

xlookup memperkirakan kecocokan yang lebih besarDalam contoh di atas, tabel A1:B5 menyatakan tarif pajak penghasilan yang diterapkan. Jumlah yang lebih tinggi dari jumlah yang disebutkan akan termasuk dalam kelompok pajak yang lebih tinggi. Dalam kasus ini, karyawan tersebut hanya menghasilkan $1 di atas $20.000. Karena kami memasukkan 1 di Argumen 5, Excel dengan benar menerapkan tarif pajak yang lebih tinggi sebesar 12%.

Pertandingan wildcard

Terkadang kami ingin Excel melakukan pencocokan sebagian atau “kabur”, seperti dalam kasus ejaan alternatif sebuah nama. Dalam contoh ini, kami mengetik 2 dalam Argumen 5 dari fungsi XLOOKUP.

Sebagai contoh:

=XLOOKUP(B12,B2:B8,D2:D8“Nama tidak ditemukan”,2)

pertandingan wildcard xlookup

Dalam contoh di atas, kita ingin mengetahui di departemen mana seorang karyawan bekerja. Rumus di sel B13 memungkinkan pengguna memasukkan karakter pengganti di sel B12 jika mereka tidak yakin dengan cara mengeja nama karyawan.

Jika hanya satu karakter yang tidak diketahui, kami menggunakan tanda tanya sebagai pengganti karakter yang tidak diketahui. Misalnya, untuk mengizinkan pencarian “Tony Scutti” atau “Toni Scutti”, pengguna akan memasukkan “Ton? Scutti” di sel B12.

Baca Juga:  Cara Menggunakan FREKUENSI Fungsi Excel

Tanda bintang mewakili string karakter apa pun, yang memungkinkan Excel mencari ejaan “Bryan”, “Brian”, atau “Brien”, dengan mengetikkan “Br*n Edwards” di sel input.

Argumen 6: Mode pencarian

=XLOOKUP(nilai_pencarian, array_pencarian, array_kembali, [if_not_found], [match_mode], [search_mode])

Secara default, XLOOKUP akan mulai mencari dari item pertama dalam array pencarian. Mungkin ada saatnya kita ingin mulai mencari dari nilai terakhir sebagai gantinya. Perilaku pencarian dikendalikan oleh Argumen 6, search_mode, yang bersifat opsional.

Opsi yang memungkinkan untuk mode pencarian adalah:

1 pertama hingga terakhir (default)

-1 terakhir ke pertama (mundur)

2 nilai pencarian biner diurutkan dalam urutan menaik

-2 nilai pencarian biner diurutkan dalam urutan menurun

Menggunakan XLOOKUP untuk mengembalikan beberapa nilai

Anda juga dapat mengembalikan beberapa nilai menggunakan rumus XLOOKUP. Jumlah nilai yang ingin Anda kembalikan harus sesuai dengan jumlah sel kosong yang tersedia di sebelah sel input, yaitu, untuk mengembalikan 3 nilai, Anda memerlukan 3 sel kosong yang berdekatan.

Contoh ditunjukkan di bawah ini:

xlookup beberapa nilaiDalam skenario di atas, kami ingin sel B12 dan C12 menunjukkan nama karyawan dan departemen untuk nomor ID yang dimasukkan di sel A12. Untuk itu, cukup sertakan seluruh rentang hasil yang mungkin sebagai larik pengembalian kami, dalam hal ini, sel B2 hingga C8. Kami memasukkan rumus di sel B12 dan tidak perlu mengetik ulang rumus di sel C12, karena penyertaan dua kolom yang berdekatan (B dan C) dalam larik pengembalian kami menginstruksikan Excel untuk “menumpahkan” hasil yang berdekatan ke dalam sel C12.

Memilih sel B12 akan menampilkan rumus yang kita masukkan di bilah rumus seperti yang diharapkan.

rumus xlookup B12

Rumus juga akan muncul jika kita memilih sel C12, tetapi akan berwarna abu-abu dan tidak dapat diedit.

rumus xlookup berwarna abu-abu

Jadi sekarang Anda tahu semua tentang fungsi baru yang hebat ini – XLOOKUP di Excel. Ini adalah penghemat waktu, dan kemungkinan akan menjadi fungsi paling populer dari keluarga pencarian.

Tinggalkan komentar