Cara Menggunakan XMATCH di Excel: Lookup Dinamis, Wildcard, Pencarian Terbalik

Fungsi XMATCH adalah salah satu fitur pencarian terbaru di Excel, diperkenalkan sejak Excel 2019. Fungsi ini merupakan versi yang lebih canggih dari fungsi klasik MATCH, dengan kemampuan pencarian horizontal dan vertikal, dukungan wildcard, pencarian terbalik, dan integrasi yang lebih baik dengan array dinamis.

Artikel ini akan membahas sintaks XMATCH, contoh penggunaan, tips penting, serta solusi untuk kesalahan umum yang mungkin terjadi.

File: fungsi-xmatch-excel.xlsx.

Apa itu Fungsi XMATCH Excel?

Fungsi XMATCH adalah fungsi yang mengembalikan posisi relatif dari suatu nilai di dalam suatu array atau range.

XMATCH adalah pengembangan dari fungsi MATCH, dengan penambahan fitur seperti sistem pencarian yang lebih baik, pencarian secara horizontal dan vertikal, serta penanganan kesalahan yang lebih canggih.

Fungsi ini bisa digunakan pada Excel Web, Excel 365, dan Excel 2019 ke atas.

Sintaks

XMATCH(lookup_value; lookup_array; [match_mode]; [search_mode])

• lookup_value (wajib): Nilai yang ingin dicari.
• lookup_array (wajib): Range atau array di mana nilai dicari
• match_mode (opsional): Menentukan tipe pencarian.
  • 0 (default): Sama persis
  • -1: Sama persis atau nilai terbesar yang lebih kecil dari lookup_value
  • 1: Sama persis atau nilai terkecil yang lebih besar dari lookup_value
  • 2: Pencarian menggunakan wildcard
• search_mode (optional): Menentukan arah pencarian
  • 1 (default): kiri ke kanan atau atas ke bawah
  • -1: kanan ke kiri atau bawah ke atas
  • 2: pencarian biner secara ascending
  • -2: pencarian biner secara descending

Kapan dan Mengapa Menggunakan Rumus XMATCH

Berikut beberapa skenario penggunaan XMATCH, tapi tidak terbatas pada:

• Mencari posisi data tertentu pada rentang besar atau tidak terurut
• Mencari menggunakan wildcard
• Mencari secara horizontal atau vertikal
• Mengolah dynamic array atau rentang tumpahan
• Mencari dari bawah ke atas atau kanan ke kiri
• Melakukan pencarian pada data besar dengan secara biner

Hal Penting Saat Menggunakan Rumus XMATCH

• XMATCH mengembalikan posisi, bukan nilai. Gunakan XLOOKUP atau kombinasikan dengan INDEX untuk mengambil nilai.
• Untuk pencarian estimasi atau biner, pastikan array sudah diurutkan sesuai mode pencarian.
• Gunakan match_mode dan search_mode untuk kendali pencarian yang lebih presisi.
• Kombinasikan dengan LET untuk membuat rumus lebih mudah dibaca.
• Gunakan array sebagai lookup_value untuk mengembalikan banyak posisi sekaligus.

Cara Menggunakan Rumus XMATCH di Excel

Mencari Posisi Nama

Rumus di bawah adalah bentuk dasar XMATCH. Rumus mencari posisi Maharani di dalam tabel.

• D2 (lookup_value) adalah nilai yang dicari, yaitu Maharani.
• A2:A6 (lookup_array) adalah range data di mana nilai D2 dicari.

Rumus mengembalikan 2.

=XMATCH(D2; A2:A6)

Pencarian Terbalik: Mencari Nama Terakhir di Data

Rumus berikut menggunakan metode pencarian terbalik atau reverse search, yaitu mencari Maharani (D2) mulai dari B6 ke B2. Pencarian terbalik dilakukan dengan memberikan search_mode = -1.

=XMATCH(D2; B2:B6; 0; -1)

Pencarian Parsial dengan Wildcard

Pencarian wildcard adalah pencarian parsial, yaitu pencarian menggunakan sebagian teks yang mencari keseluruhan teks. XMATCH mengembalikan hasil pertama yang ditemui.

Pencarian dengan wildcard dilakukan dengan memberikan argumen 2 pada match_mode.

Rumus berikut mencari nama yang memiliki ra di dalam range A2:A6 dan mengembalikan 2, yaitu posisi Maharani. Perhatikan rumus mengabaikan Prasetya.

=XMATCH(D2; A2:A6; 2)

Baca: Wildcard Excel.

Pencarian Estimasi dengan XMATCH (Approximate Match)

Secara default, XMATCH menggunakan pencarian sama persis. Untuk pencarian berdasarkan estimasi, gunakan match_mode = 1 atau match_mode = -1.

Rumus di bawah mencari nilai penjualan sebesar 159 dan mengembalikan 2, yaitu posisi penjualan 160. 160 adalah nilai terkecil yang lebih besar dari 159.

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

Perhatikan data B2:B6 terurut secara ascending.

=XMATCH(D2; B2:B6; 1)

Pencarian Horizontal dengan XMATCH di Excel

Rumus berikut mencari posisi kolom Q3. XMATCH menyesuaikan pencarian, horizontal atau vertikal, berdasarkan bentuk rentang. B1:D1 adalah rentang horizontal.

=XMATCH(A9; B1:D1)

Pencarian Estimasi Terbalik dengan XMATCH

XMATCH mampu melakukan pencarian estimasi secara terbalik. Sebagai catatan, MATCH tidak bisa melakukan pencarian seperti ini.

=XMATCH(D2; B2:B6; 1; -1)

Lookup Dinamis dengan INDEX dan XMATCH

XMATCH dapat dipadukan dengan INDEX untuk membentuk fungsi lookup seperti XLOOKUP atau VLOOKUP.

Rumus di bawah mencari nilai penjualan Prasetya (B9) pada kuartal ketiga (A9).

=INDEX(A2:D6; XMATCH(B9; A2:A6); XMATCH(A9; B1:D1))

Menggabungkan XMATCH dan FILTER untuk Penyaringan Baris Otomatis

XMATCH memberikan kemampuan pencarian secara parsial, terbalik, atau mendapatkan posisi tertentu bagi FILTER.

=FILTER(A2:C10; XMATCH("Aditya"; A2:A10)=ROW(A2:A10)-ROW(A2)+1)

Potongan rumus XMATCH("Aditya"; A2:A10)=ROW(A2:A10)-ROW(A2)+1 melakukan perbandingan antara hasil XMATCH dengan posisi relatif rentang. Potongan ini mengembalikan array TRUE dan FALSE sebagai bentuk Boolean filtering bagi FILTER.

Menggunakan XMATCH untuk Mengembalikan Banyak Posisi Sekaligus

Agar rumus XMATCH mengembalikan lebih dari satu hasil sekaligus, gunakan array range atau konstan sebagai lookup_value.

=XMATCH(D2:D4; A2:A10)

Penyebab dan Solusi Kesalahan Umum Rumus XMATCH

Berikut beberapa sebab mengapa rumus XMATCH tidak bekerja.

Perbandingan Fungsi XMATCH vs MATCH Excel

Kesimpulan

Fungsi XMATCH adalah evolusi dari MATCH yang menawarkan fleksibilitas lebih besar dalam pencarian data. Dengan dukungan pencarian horizontal, terbalik, wildcard, dan integrasi array dinamis, XMATCH menjadi alat penting untuk membangun rumus lookup yang cerdas dan responsif.

Gunakan XMATCH untuk menyederhanakan pencarian, memperkuat validasi, dan meningkatkan efisiensi kerja di Excel.