Cara Menggunakan XLOOKUP() dengan Banyak Kriteria di Excel

XLOOKUP() di Excel saat ini adalah fungsi terbaru dalam kelompok fungsi pencarian (LOOKUP(), VLOOKUP(), HLOOKUP(), dan XLOOKUP()) yang menawarkan banyak keunggulan, fungsionalitas yang lebih luas, dan fleksibilitas yang lebih tinggi.

Dalam tutorial ini, terlebih dahulu kita akan membahas untuk apa fungsi Excel XLOOKUP() digunakan dan bagaimana fungsinya lebih baik daripada fungsi pencarian yang lebih lama; berikutnya, kita akan melihat sintaks dasarnya, lalu masuk ke inti bahasan — penggunaan fungsi XLOOKUP() dengan banyak kriteria.

Jika Anda perlu mempelajari dasar-dasar Excel, kursus Introduction to Excel yang ramah pemula adalah tempat yang tepat untuk mulai.

Ringkasnya

• XLOOKUP() adalah fungsi pencarian terbaru di Excel. Fungsi ini lebih fleksibel dan lebih cepat daripada VLOOKUP(), HLOOKUP(), dan LOOKUP(), dengan dukungan pencarian multi-arah dan kecocokan parsial.

• Untuk mencari dengan banyak kriteria, ada dua pendekatan utama: penggabungan (menggabungkan nilai pencarian dengan &, mis. =XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)) atau ekspresi boolean (mengalikan kondisi, mis. =XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)).

• Penggabungan lebih sederhana dan lebih mudah dibaca, tetapi ekspresi boolean memberi Anda fleksibilitas lebih.

• Parameter opsional memperluas fungsi lebih jauh: [if_not_found] mengembalikan teks khusus saat tidak ada yang cocok, [match_mode] memungkinkan kecocokan mendekati atau wildcard, dan [search_mode] memungkinkan Anda membalik arah pencarian.

Mengapa Menggunakan XLOOKUP() di Excel

Fungsi XLOOKUP() mencari dalam suatu rentang atau array data dan mengembalikan item yang sesuai dengan kecocokan pertama. Jika tidak ditemukan kecocokan, XLOOKUP() dapat mengembalikan kecocokan mendekati jika jenis kecocokan tertentu disediakan. Dalam banyak aspek, fungsi XLOOKUP() melampaui pendahulunya di Excel (VLOOKUP(), HLOOKUP(), dan LOOKUP()).

Secara khusus, fungsi ini memungkinkan:

• mencari data secara horizontal dan vertikal, serta ke segala arah
• banyak kriteria pencarian
• kecocokan mendekati, dengan bawaan kecocokan tepat
• kecocokan parsial
• mengembalikan banyak kolom dan baris
• mengembalikan teks yang disesuaikan saat tidak ditemukan kecocokan.

Selain itu, fungsi XLOOKUP() bekerja lebih cepat daripada fungsi pencarian lama di Excel, yang penting saat kita mencari dalam jumlah data yang besar.

Cara Menggunakan XLOOKUP() dengan Satu Kondisi

Mari kita tinjau secara singkat sintaks dasar fungsi XLOOKUP():

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

dengan parameter wajib sebagai berikut:

• lookup_value—nilai yang akan dicari

• lookup_array—array atau rentang tempat pencarian dilakukan

• return_array—array atau rentang yang akan dikembalikan

dan parameter opsional sebagai berikut:

• [if_not_found]—teks yang disediakan untuk dikembalikan saat tidak ditemukan kecocokan

• [match_mode]—jenis kecocokan (tepat atau mendekati), dan apa yang akan dikembalikan jika tidak ditemukan kecocokan.

• [search_mode]—mode pencarian yang digunakan (pencarian langsung atau terbalik, pencarian biner pada lookup array yang diurutkan naik atau turun).

Kita akan melihat contoh penggunaan parameter opsional nanti dalam tutorial ini.

Untuk mempelajari atau menyegarkan beberapa fungsi dan rumus bermanfaat lainnya di Excel, silakan lihat tutorial The 15 Basic Excel Formulas Everyone Needs to Know dan Excel Formulas Cheat Sheet.

Cara Menggunakan XLOOKUP() dengan Banyak Kriteria

Seperti terlihat dari sintaks dasarnya, fungsi Excel XLOOKUP() secara bawaan dirancang untuk bekerja dengan satu nilai pencarian dari suatu variabel tertentu.

Namun, kita dapat menyesuaikan fungsi ini untuk digunakan pada beberapa variabel secara bersamaan, mencari nilai pencarian yang berbeda pada setiap variabel. Dengan kata lain, kita dapat melakukan pencarian dengan XLOOKUP() dengan menerapkan banyak kriteria. Untuk melakukannya, ada dua pendekatan utama: penggabungan dan ekspresi boolean, dan kita akan membahas keduanya segera.

Sebelum masuk ke detail teknis, mari lihat tabel Excel yang akan kita gunakan. Tabel ini memberikan informasi tentang 10 kucing, termasuk nama, warna, dan usianya:

Dalam eksperimen kita, kita akan menggunakan XLOOKUP() untuk menemukan salah satu dari tiga karakteristik kucing dengan menggunakan dua karakteristik lainnya.

Tidak masalah jika tabel kita sangat sederhana, dan kita jelas bisa menemukan yang kita cari pada tiap kasus tanpa fungsi apa pun. Di sini, kita perlu memahami prinsip inti penggunaan XLOOKUP() dengan banyak kriteria. Dengan mengetahui prinsip-prinsip ini, kita dapat dengan mudah menerapkannya pada skenario dunia nyata yang lebih kompleks, misalnya saat kita memiliki lebih dari dua kriteria pencarian atau persyaratan khusus tertentu.

XLOOKUP() dengan banyak kriteria menggunakan penggabungan

Pendekatan penggabungan cukup lugas: kita perlu menggabungkan nilai dan array pencarian yang bersesuaian satu sama lain. Mari lihat cara kerjanya.

Misalkan kita ingin menemukan usia kucing berwarna putih-abu yang bernama Nala. Untuk kemudahan, kita menambahkan dua kriteria ini pada lembar yang sama tempat kita menyimpan tabel:

Dalam kasus ini, rumus untuk XLOOKUP() adalah sebagai berikut:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)

Rumus ini akan mengembalikan 2,5, usia pertama Nala putih-abu (kucing saya 😺). Ingat bahwa XLOOKUP() mengembalikan item yang sesuai dengan kecocokan pertama.

Untuk memahami apa yang diwakili oleh dua komponen pertama dari rumus di atas—yang menggunakan ampersand—kita bisa menjalankan masing-masing di sel Excel terpisah:

=B2:B11&C2:C11

Hasilnya adalah NalaWhite dan Grey.

=B2:B11&C2:C11

Hasilnya:

Ya, semudah itu: kita hanya menggabungkan nilai pencarian dan lookup array yang bersesuaian satu sama lain, mengikuti sintaks dasar XLOOKUP() yang kita bahas sebelumnya.

XLOOKUP() dengan banyak kriteria menggunakan ekspresi boolean

Sekarang, mari lakukan tugas yang sama—mencari usia kucing putih-abu bernama Nala—dengan pendekatan kedua: ekspresi boolean. Dalam hal ini, rumus XLOOKUP() adalah sebagai berikut:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Seperti yang diharapkan, rumus ini juga akan mengembalikan 2,5, yaitu usia Nala putih-abu pertama.

Mari kita telusuri komponen-komponen rumus di atas. Di sini, 1 berarti TRUE, yaitu kita mencari nilai TRUE pada lookup array yang diwakili oleh komponen kedua rumus—(B2:B11=G2)*(C2:C11=G3).

Komponen ini, pada gilirannya, berisi dua pengali, masing-masing memeriksa kondisi tertentu: yang pertama memeriksa apakah nama sesuai dengan nama yang kita cari, sedangkan yang kedua memeriksa apakah warna sesuai dengan warna yang kita cari. Mari jalankan tiap pengali di sel Excel terpisah:

=B2:B11=G2

Hasilnya:

=C2:C11=G3

Hasilnya:

Sekarang, mari jalankan seluruh komponen kedua dari rumus:

=(B2:B11=G2)*(C2:C11=G3)

Hasilnya:

Meskipun kita melihat dua nilai 1 pada hasil di atas (berarti dua nilai TRUE), fungsi XLOOKUP() mengembalikan item yang sesuai dengan kecocokan pertama.

Pendekatan menggunakan ekspresi boolean saat menjalankan fungsi XLOOKUP() untuk banyak kriteria terlihat jauh lebih rumit dan kurang intuitif dibandingkan pendekatan berbasis penggabungan. Ini akan menjadi lebih buruk lagi jika kita menggunakan lebih dari dua kriteria pencarian. Lalu, mengapa tidak selalu menggunakan penggabungan saja?

Pendekatan ekspresi boolean memberi kita fleksibilitas yang jauh lebih besar saat menggunakan XLOOKUP() dengan banyak kriteria. Mari kita lihat keunggulannya dibandingkan pendekatan penggabungan.

Memeriksa Nilai FALSE

Pada rumus di atas, kita memeriksa apakah ekspresi boolean bernilai TRUE, dengan memasukkan nilai 1:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Dalam beberapa kasus, kita mungkin perlu memeriksa apakah ekspresi boolean justru bernilai FALSE. Misalnya, kita ingin mencari usia kucing pertama dalam tabel yang bukan Nala putih-abu. Kita harus memodifikasi rumus sebagai berikut:

=XLOOKUP(0, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Hasilnya adalah 1, yaitu usia kucing pertama yang bukan Nala putih-abu (lebih tepatnya, Nala tricolor, tetapi bukan putih-abu).

Menggunakan Operator Logika

Dengan ekspresi boolean, kita tidak terbatas hanya pada pemeriksaan kesetaraan. Misalnya, kita ingin mencari warna kucing bernama Nala yang berusia kurang dari 2 tahun:

Dalam kasus ini, rumus XLOOKUP() adalah sebagai berikut:

=XLOOKUP(1, (B2:B11=G2)*(D2:D11<G3), C2:C11)

Hasilnya adalah Tricolor.

Memenuhi Setidaknya Satu Kriteria

Sejauh ini, yang kita lakukan adalah memeriksa kesesuaian dengan semua kriteria yang diberikan. Pada skenario lain, kita mungkin perlu memenuhi setidaknya satu kriteria.

Untuk melihat cara kerjanya, mari kembali ke tugas awal kita—mencari usia kucing putih-abu bernama Nala:

Kali ini, kita ingin mencari usia kucing yang entah bernama Nala atau berwarna putih-abu. Rumus XLOOKUP() dalam kasus ini adalah:

=XLOOKUP(1, (B2:B11=G2)+(C2:C11=G3), D2:D11)

Rumus tersebut mengembalikan 1, yaitu usia Nala tricolor.

Sebagai pengingat, saat kita mencari usia kucing yang baik bernama Nala dan berwarna putih-abu, rumusnya adalah sebagai berikut:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Mengganti * dengan + membuat perbedaannya.

Jika Anda tertarik mengeksplorasi potensi penuh Excel, pertimbangkan jalur keterampilan Excel Fundamentals yang komprehensif, berorientasi praktik, dan menyeluruh.

Parameter opsional untuk XLOOKUP() dengan banyak kriteria

Seperti penggunaan biasa XLOOKUP() dengan satu kriteria, kita dapat menggunakan opsi tambahan fungsi ini saat menjalankannya pada banyak kriteria. Di sinilah parameter opsional [if_not_found], [match_mode], dan [search_mode] berperan.

Mari lihat cepat contoh penggunaan masing-masing parameter ini dengan XLOOKUP() dan banyak kriteria. Untuk kesederhanaan, kita akan menerapkan pendekatan penggabungan di setiap contoh.

Mengembalikan Teks yang Disediakan Saat Tidak Ditemukan Kecocokan

Di sini kita mencari usia Nala berwarna hitam—kucing yang tidak ada dalam tabel kita:

Dalam kasus ini, kita menambahkan ke rumus XLOOKUP() parameter opsional [if_not_found], yang kita isi dengan teks yang akan dikembalikan jika tidak ditemukan kecocokan:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, "No cat is found")

Memang, fungsi tersebut mengembalikan teks yang disediakan: No cat is found. Jika kita tidak memberikan teks apa pun, fungsi akan mengembalikan #N/A.

Mengembalikan Kecocokan Mendekati

Misalnya, kita mencari usia Nala tricolor, tetapi kita tidak yakin apakah warnanya ditulis sebagai "Tricolor" atau "Tricolour" di tabel. Di sinilah kita perlu menggunakan kecocokan mendekati berbasis wildcard dan memberikan argumen [match_mode] bernilai 2 ke fungsi XLOOKUP().

Pada tabel di atas, kita menggunakan tanda bintang (*), yang mewakili sejumlah karakter apa pun, termasuk 0. Wildcard lainnya dapat ditemukan di dokumentasi Microsoft Office.

Rumus XLOOKUP() di sini adalah:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , 2)

Dan hasilnya 1, usia Nala tricolor pertama (dan satu-satunya) dalam tabel kita.

Perhatikan bahwa argumen 2 dalam rumus di atas berarti kita tertarik pada kecocokan mendekati, bukan mencari usia kucing Nala dengan warna "Tricolo*r." Dalam kasus kita, tanda bintang menangkap kata "Tricolor," tetapi akan melakukan hal yang sama pada kata "Tricolour" atau, misalnya, "Tricolooor."

Melakukan Pencarian Terbalik

Mari lakukan latihan yang sama seperti di awal—mencari usia Nala putih-abu—tetapi kali ini, kita akan memulai pencarian dari item terakhir.

Untuk melakukannya, kita perlu menambahkan ke fungsi XLOOKUP() parameter opsional [search_mode] yang disetel ke -1, seperti di bawah ini:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , , -1)

Fungsi mengembalikan 7, usia Nala putih-abu pertama dari akhir lookup array.

XLOOKUP() vs. INDEX() dan MATCH()

Pada versi Excel yang lebih lama, untuk mereplikasi fungsionalitas serupa dengan penggunaan fungsi XLOOKUP() dengan banyak kriteria, kita perlu menggabungkan fungsi INDEX() dan MATCH(). Tanpa menyelami sintaks kedua fungsi, mari lihat bagaimana kita bisa menemukan usia Nala putih-abu pertama dengan menggunakan metode lama INDEX() + MATCH() dan fungsi baru XLOOKUP() (untuk referensi sel, kita dapat merujuk ke tabel sebelumnya):

=INDEX(D2:D11, MATCH(1, (G2=B2:B11)*(G3=C2:C11), 0))
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Meskipun argumennya terlihat agak mirip, mari sebutkan keunggulan utama menjalankan fungsi XLOOKUP() dibandingkan metode lama:

• Satu fungsi saja cukup untuk operasi ini — tidak perlu menggabungkan fungsi.

• Adanya kemungkinan menerapkan pendekatan penggabungan bila memungkinkan.

• Adanya kemungkinan menggunakan parameter opsional (fungsi MATCH() tidak memilikinya).