Rumus XLOOKUP Excel: Penjelasan, Sintaks, dan Penggunaan

Kita mengenal LOOKUP, HLOOKUP, dan VLOOKUP, yaitu fungsi-fungsi untuk mencari dan mengambil data dari tabel lain berdasarkan kata kriteria tertentu.

Excel memiliki fungsi lain yang serupa, yaitu XLOOKUP. Fungsi ini merupakan pengembangan dari ketiga fungsi yang sebelumnya.

Tulisan ini membahas rumus XLOOKUP Excel, bentuk sintaks, serta ragam penggunaannya.

File: fungsi-xlookup-excel.xlsx.

Apa Itu XLOOKUP di Excel?

Fungsi XLOOKUP di Excel digunakan untuk mencari dan mengambil data dari tabel berdasarkan kriteria tertentu. Berbeda dengan LOOKUP, VLOOKUP, dan HLOOKUP, XLOOKUP lebih fleksibel, memungkinkan pencarian secara vertikal atau horisontal, serta pengambilan data di sebelah kiri atau atas dari kolom atau baris pencarian.

Sintaks

XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; [match_mode]; [search_mode])

• lookup_value (wajib): Nilai yang dicari dalam lookup_array.
• lookup_array (wajib): Rentang atau array tempat pencarian dilakukan.
• return_array (wajib): Rentang yang berisi nilai yang akan dikembalikan.
• if_not_found (opsional): Nilai alternatif jika pencarian tidak menemukan hasil untuk menghindari #N/A.
• match_mode (opsional): Tipe pencocokan antara lookup_value dan lookup_array.
  • 0: Persis sama (default).
  • -1: Nilai sama atau terbesar yang lebih kecil dari lookup_value.
  • 1: Nilai sama atau terkecil yang lebih besar dari lookup_value.
  • 2: Pencarian menggunakan wildcard.
• search_mode (opsional): Cara menentukan urutan pencarian.
  • 1: Dari atas ke bawah atau kiri ke kanan (default).
  • -1: Dari bawah ke atas atau kanan ke kiri.
  • 2: Pencarian binari (data harus ascending).
  • -2: Pencarian binari (data harus descending).

Kelebihan XLOOKUP Dibanding VLOOKUP dan HLOOKUP

1. Lebih fleksibel: Bisa mencari secara vertikal dan horisontal.
2. lookup_array dan return_array terpisah: Tidak perlu mencantumkan semua kolom atau baris.
3. Mendukung pencarian ke kiri atau atas: VLOOKUP hanya bisa ke kanan dan HLOOKUP hanya ke kanan.
4. Error handling bawaan: Bisa menentukan nilai alternatif jika tidak ditemukan.
5. Lebih cepat: Hanya membutuhkan lookup_array dan return_array, tanpa memasukkan semua kolom atau baris.
6. Bisa mencari dengan beberapa kriteria sekaligus: Tidak perlu menggabungkan banyak rumus.
7. Mendukung wildcard: Memungkinkan pencarian berdasarkan sebagian kata.

Cara Menggunakan XLOOKUP di Excel

Penggunaan Dasar XLOOKUP

Misalkan kita ingin mencari PDRB Provinsi Jambi berdasarkan tabel.

=XLOOKUP(E2; A2:A7; B2:B7)

• E2: lookup value (nama provinsi).
• A2:A7: lookup array (daftar provinsi).
• B2:B7: return array (PDRB masing-masing provinsi).

Hasil: 233.730.000 (PDRB Provinsi Jambi).

Menangani Error #N/A dengan XLOOKUP

Jika lookup_value tidak ditemukan, XLOOKUP akan mengembalikan error #N/A. Untuk menghindarinya, gunakan parameter if_not_found:

=XLOOKUP(E2; A2:A7; B2:B7; "Data Tidak Ada")

Jika tidak ada hasil, akan menampilkan Data Tidak Ada daripada #N/A.

Menggunakan XLOOKUP untuk Pencarian Horisontal

XLOOKUP tidak hanya bekerja secara vertikal, tetapi juga horisontal. Misalnya, mencari PDRB Lampung di tabel horisontal.

=XLOOKUP(B5; B1:F1; B2:F2)

• B5: lookup value (nama provinsi).
• B1:F1: lookup array (baris nama provinsi).
• B2:F2: return array (baris PDRB).

Hasil: PDRB Provinsi Lampung sebesar 371.900.000.

Mengambil Data di Kiri dengan XLOOKUP

XLOOKUP bisa mengambil data di kiri dari kolom pencarian (tabel vertikal) atau atas dari baris pencarian (tabel horisontal).

=XLOOKUP(E2; B2:B7; A2:A7)

• E2: lookup_value (besaran PDRB).
• B2:B11: lookup array (daftar PDRB).
• A2:A11: return array (nama provinsi).

Hasil: Bengkulu (provinsi dengan PDRB 79.580.000).

Menggunakan Wildcard dalam XLOOKUP

Wildcards memungkinkan pencarian berdasarkan sebagian kata:

• Tanda asterisk (*) mewakili satu atau lebih karakter.
• Tanda tanya (?) mewakili satu karakter.
• Tanda tilde (~) menghilangkan efek satu tanda asterisk atau tanda tanya yang ditulis setelah tanda ini.

=XLOOKUP("Sumatera*"; A2:A7; B2:B7; ; 2)

=XLOOKUP("*u"; A2:A7; B2:B7; ; 2)

=XLOOKUP("*am*"; A2:A7; B2:B7; ; 2)

=XLOOKUP("*ta? "; A2:A7; B2:B7; ; 2)

=XLOOKUP("*~?*"; A2:A7; B2:B7; ; 2)

=XLOOKUP("*~**"; A2:A7; B2:B7; ; 2)

Jika lebih dari satu hasil ditemukan, XLOOKUP hanya mengambil yang pertama.

Baca: Menggunakan Wildcard di Excel.

Rumus XLOOKUP Pencarian dari Bawah ke Atas

XLOOKUP dapat mencari nilai dari bawah ke atas (atau kanan ke kiri) dengan memberikan nilai -1 bagi parameter search_mode.

=XLOOKUP(F2; A2:A8; C2:C8; ; ; -1)

Rumus mencari PDRB Lampung dari bawah dan mendapatkan 389.349.000. Tanpa -1, rumus mengembalikan 408.122.000.

Menggunakan XLOOKUP dengan Beberapa Kriteria

Jika membutuhkan pencarian berdasarkan dua atau lebih kriteria, gunakan operator perkalian (*):

=XLOOKUP(1; (B2:B7=G4) * (C2:C7<G3) * (D2:D7>G2); A2:A7)

• 1: lookup_value (kunci pembanding bagi hasil semua kriteria)
• B2:B7=G4: Kriteria 1 (tahun 2021).
• C2:C7<G3: Kriteria 2 (PDRB di bawah 507.944.000).
• D2:D7>G2: Kriteria 3 (Jumlah penduduk di atas 6.867.136).

Jika semua kriteria terpenuhi (1=1×1×1), XLOOKUP mengembalikan nama provinsi yang sesuai.

XLOOKUP Mengembalikan Beberapa Kolom Hasil

XLOOKUP dapat mengembalikan beberapa kolom (atau baris) hasil dengan memberikan array range bagi return_array.

=XLOOKUP(A2; A5:A10; B5:C10)

Perhatikan garis tipis yang mengelilingi B2:C2 sebagai penanda hasil rumus array.

Menggunakan Kombinasi XLOOKUP dan SUM

Karena XLOOKUP bisa mengembalikan array maka menggabungkannya dengan fungsi matematika (SUM atau COUNT) atau fungsi statistik (AVERAGE, MIN, atau MAX) adalah mungkin.

=SUM(XLOOKUP(E3; B2:C2; B3:C8))

Hasil: 2.622.973.000 (Total PDRB seluruh provinsi pada tahun 2023).

Approximate Match di XLOOKUP

Approximate match atau pencarian di mana rumus dapat memberikan hasil yang sama atau berdasarkan estimasi. Pencarian ini dicapai dengan memberikan nilai -1 atau 1 bagi match_mode.

• match_mode = -1 mencari nilai sama atau nilai terbesar yang lebih kecil dari lookup_value.

  =XLOOKUP(E2; B2:B7; A2:A7; ; -1)

  

• match_mode = 1 akan mencari nilai sama atau nilai terkecil yang lebih besar dari lookup_value.

  =XLOOKUP(E2; B2:B7; A2:A7; ; 1)

  

Kesimpulan

Fungsi XLOOKUP di Excel menawarkan pencarian data yang lebih fleksibel, cepat, dan akurat dibandingkan VLOOKUP atau HLOOKUP. Dengan memahami sintaks, cara menangani error, penggunaan wildcard, serta pencarian berdasarkan beberapa kriteria, kita dapat meningkatkan efisiensi pencarian data secara signifikan.