Tutorial Penggunaan XLOOKUP dengan Banyak Kriteria di Excel

By /

Seringkali muncul persepsi keliru bahwa fungsi XLOOKUP di Microsoft Excel hanya mampu menangani satu kriteria pencarian dalam sebuah dataset. Padahal, faktanya, fungsi ini sangat fleksibel dan dapat mencari nilai berdasarkan multi-kriteria. Hebatnya lagi, ada dua metode berbeda yang bisa digunakan, masing-masing dengan tingkat kompleksitas dan kegunaannya sendiri.

Untuk bisa memanfaatkan fungsi XLOOKUP secara optimal, pengguna aplikasi desktop Excel pada PC atau Mac harus memastikan telah menggunakan Excel versi 2021 atau yang lebih baru, termasuk Excel untuk Microsoft 365. Selain itu, fungsi ini juga sudah tersedia secara default di Excel untuk web serta aplikasi tablet dan seluler Excel.

Microsoft 365 menawarkan akses penuh ke berbagai aplikasi Office premium seperti Word, Excel, dan PowerPoint, yang dapat diinstal hingga pada lima perangkat berbeda. Selain itu, pengguna juga mendapatkan kapasitas penyimpanan OneDrive sebesar 1 TB, serta berbagai fitur pelengkap lainnya yang menunjang produktivitas.

Sintaks XLOOKUP dan Contoh Kriteria Tunggal

Sekilas, sintaksis fungsi XLOOKUP mungkin terlihat rumit, namun sebenarnya ia mengikuti urutan yang logis dan mudah dipahami:

=XLOOKUP(a,b,c,d,e,f)

Berikut adalah penjelasan setiap argumennya:

  • a (Wajib): Ini adalah nilai yang ingin dicari (lookup value).
  • b (Wajib): Ini adalah rentang sel atau array tempat nilai a akan dicari (lookup array).
  • c (Wajib): Ini adalah rentang sel atau array dari mana nilai yang sesuai akan dikembalikan (return array).
  • d (Opsional): Teks atau nilai yang akan dikembalikan jika nilai a tidak ditemukan di b.
  • e (Opsional): Mode pencocokan (match mode). Pilihan meliputi:
    • 0: Pencocokan persis (default).
    • -1: Pencocokan persis atau item terdekat yang lebih kecil.
    • 1: Pencocokan persis atau item terdekat yang lebih besar.
    • 2: Pencocokan wildcard (menggunakan karakter joker).
  • f (Opsional): Mode pencarian (search mode). Pilihan meliputi:
    • 1: Mencari dari pertama ke terakhir (default).
    • -1: Mencari dari terakhir ke pertama.
    • 2: Pencarian biner di mana b dalam urutan menaik.
    • -2: Pencarian biner di mana b dalam urutan menurun.

Sebagai ilustrasi, jika sebuah ID dipilih dari daftar drop-down di sel E1 (misalnya, ID 1323), kemudian formula berikut dimasukkan ke sel E2:

=XLOOKUP(E1,T_Scores[ID],T_Scores[Score],"No match",0,1)

Maka, Excel akan mengembalikan skor 51. Ini terjadi karena:

  • E1 adalah sel yang berisi nilai (1323) yang akan dicari dalam tabel.
  • T_Scores[ID] menginstruksikan Excel untuk mencari nilai tersebut di kolom ID dalam tabel T_Scores.
  • T_Scores[Score] menginstruksikan Excel untuk mengembalikan nilai yang sesuai dari kolom Score pada tabel yang sama.
  • “No match” adalah pesan yang akan ditampilkan jika nilai yang dicari tidak ada dalam array pencarian.
  • 0 memberi tahu Excel untuk mencari pencocokan yang persis sama.
  • 1 memberi tahu Excel untuk mencari nilai dari atas ke bawah pada kolom ID.

Perlu dicatat, dua argumen terakhir pada formula di atas sebenarnya bisa diabaikan karena pencocokan persis dan pencarian dari atas ke bawah adalah perilaku default dari XLOOKUP.

Contoh 1: Multi-Kriteria XLOOKUP dengan Logika Boolean

Penggunaan fungsi XLOOKUP menjadi sedikit berbeda ketika harus berhadapan dengan lebih dari satu kriteria. Bayangkan Anda memiliki sebuah merek yang menjual enam jenis minuman di enam negara berbeda, dengan manajer yang bertanggung jawab untuk setiap minuman di setiap negara.

Tujuan Anda adalah membuat fungsi pencarian yang memungkinkan Anda memasukkan nama minuman dan negara, kemudian mengembalikan nama manajer serta jumlah penjualan yang relevan. Namun, seperti yang telah dijelaskan sebelumnya, XLOOKUP umumnya bekerja dengan satu nilai pencarian, sehingga ini awalnya tampak mustahil. Untuk mengatasi keterbatasan ini, kita dapat memanfaatkan logika Boolean untuk membuat array pencarian sementara.

Berikut adalah formula yang dapat melakukannya, dipecah menjadi beberapa baris agar mudah dibaca: 

=XLOOKUP(
1,
(T_Managers[Drink]=G1)*(T_Managers[Country]=G2),
T_Managers[[Manager]:[Sales]],
"No result"
)

Mari kita telaah argumen b, yaitu lookup array:

(T_Managers[Drink]=G1)*(T_Managers[Country]=G2)

Pertama, formula akan memeriksa kolom Drink dari tabel T_Managers untuk mencari kecocokan dengan nilai di sel G1 (misalnya, “Coffee”). Hasilnya adalah array sementara seperti ini:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE...}

Ini karena enam nilai pertama di kolom Drink adalah “apple juice” (FALSE), enam minuman berikutnya adalah “coffee” (TRUE), dan baris-baris sisanya adalah minuman lain (FALSE).

Selanjutnya, ia memeriksa kolom Country dari tabel yang sama untuk mencari kecocokan dengan nilai di sel G2 (misalnya, “Spain”), dan secara sementara mengembalikan:

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE...}

Karena nilai kelima dan kesebelas cocok dengan kriteria Country.

Karena kita mengalikan dua array sementara ini, operasi ini mengubah nilai TRUE menjadi 1 dan nilai FALSE menjadi 0:

{0;0;0;0;0;0;1;1;1;1;1;1;0...}
dan
{0;0;0;0;1;0;0;0;0;0;1;0...}

Ini menciptakan single lookup array, di mana angka pertama dari setiap array dikalikan, angka kedua dari setiap array dikalikan, dan seterusnya.

{0;0;0;0;0;0;0;0;0;0;1;0...}

Dengan demikian, pasangan kesebelas adalah yang pertama mengembalikan nilai 1.

Sekarang, mari kita kembali ke argumen a, yaitu lookup value:

=XLOOKUP(1

Di sini, kita memberitahu Excel untuk menemukan angka 1 dalam lookup array. Seperti yang kita lihat sebelumnya, nilai kesebelas dalam array gabungan adalah yang pertama cocok dengan lookup value ini. Oleh karena itu, XLOOKUP menemukan nilai kesebelas di setiap kolom dari return array (argumen c):

T_Managers[[Manager]:[Sales]]

Dalam contoh ini, nilai kesebelas di kolom Manager adalah Olivia, dan nilai kesebelas di kolom Sales adalah 346.

Argumen terakhir (d), yaitu teks yang akan dikembalikan jika lookup value tidak ditemukan dalam array, tidak berlaku karena ada kecocokan. Bahkan, jika Anda menggunakan validasi data untuk membuat daftar pilihan drop-down, Anda dapat mengabaikan argumen “no-match” sepenuhnya.

Selain itu, karena Anda ingin mengembalikan pencocokan persis saat mencari dari atas ke bawah, Anda tidak perlu menyertakan argumen e atau f dalam formula.

Pada contoh di atas, kita menggunakan simbol sama dengan (=) untuk menghasilkan kriteria. Namun, jika Anda mencari nilai numerik, Anda juga dapat menggunakan operator perbandingan, seperti > (lebih besar dari). Terlebih lagi, alih-alih membuat perkalian (*) antara setiap array sementara untuk mengembalikan hasil yang cocok dengan semua kriteria, Anda dapat menggunakan simbol penambahan (+) untuk mengembalikan hasil pertama yang memenuhi setidaknya salah satu kriteria.

Contoh 2: Multi-Kriteria XLOOKUP dengan Konkatenasi

Cara lain untuk memasukkan multi-kriteria ke dalam formula XLOOKUP Anda adalah dengan menggabungkan (menggabungkan) semua nilai pencarian menggunakan simbol ampersand (&), dan melakukan hal yang sama dengan semua lookup array.

Menggunakan skenario yang sama dengan Contoh 1, tujuan Anda adalah mengembalikan nama manajer dan total penjualan saat Anda memasukkan nama minuman dan negara.

Berikut adalah formula yang Anda targetkan, dipecah menjadi beberapa baris agar lebih mudah dipahami: 

=XLOOKUP(
G1&G2,
T_Managers[Drink]&T_Managers[Country],
T_Managers[[Manager]:[Sales]],
"No result"
)

Pertama, argumen a, yaitu lookup value, berisi dua referensi sel, dipisahkan oleh ampersand:

G1&G2

Secara efektif, ini menghasilkan string pencarian “CoffeeSpain”, karena “Coffee” dan “Spain” adalah nilai dalam sel yang direferensikan, dan ampersand menghubungkannya tanpa spasi.

Kemudian, untuk argumen b, yaitu lookup array, logika yang sama berlaku, dengan setiap array digabungkan oleh simbol ampersand:

T_Managers[Drink]&T_Managers[Country]

Dengan demikian, kita memberitahu Excel untuk menemukan string “CoffeeSpain” dalam array DrinkCountry dari tabel T_Managers, di mana string pertama adalah “Apple juiceAustralia”, string kedua adalah “Apple juiceCanada”, dan seterusnya.

Selanjutnya, argumen c, yaitu return array, bekerja dengan cara yang sama seperti dalam formula XLOOKUP lainnya, memberitahu Excel untuk mengembalikan nilai yang sesuai dari kolom Manager dan Sales.

T_Managers[[Manager]:[Sales]]

Akhirnya, jika string pencarian tidak ada dalam tabel T_Managers, argumen d memberitahu Excel untuk mengembalikan kata “No result.”

=XLOOKUP(G1&G2,T_Managers[Drink]&T_Managers[Country],T_Managers[[Manager]:[Sales]],"No result")

Namun, dalam kasus ini, itu ada, jadi Excel mengembalikan Olivia dari kolom Manager dan 346 dari kolom Sales.

Karena setiap kombinasi minuman-negara hanya muncul sekali dalam tabel, Anda tidak perlu menyertakan argumen e atau f. Memang, fungsi XLOOKUP secara default akan mencari kecocokan persis, dan tidak masalah apakah Anda mencari dari atas atau bawah tabel.

Related Posts

Scroll to Top