Tutorial XMATCH di Excel: Ikhtisar, Sintaks, dan Penerapan Praktis

Ada banyak situasi saat menganalisis data ketika Anda perlu menemukan posisi relatif suatu item dalam sebuah larik atau rentang sel berdasarkan nilai atau pola tertentu. 

Cara paling efektif untuk melakukan operasi pencocokan pola di Excel adalah menggunakan fungsi XMATCH. Fungsi yang sederhana namun kuat ini memiliki beberapa fitur yang memungkinkan pencocokan yang presisi dan fleksibel. 

Dalam tutorial ini, kita akan membahas dasar-dasar fungsi XMATCH di Excel, menganalisis sintaks dan parameter-parameter yang berbeda, serta memberikan contoh praktis untuk menggambarkan kemampuannya. Kita juga akan membandingkan XMATCH dengan MATCH, fungsi populer lain yang digunakan untuk tugas serupa.  Terakhir, kita akan melihat jebakan umum saat menggunakan XMATCH dan merangkum praktik terbaik agar penggunaan tetap tepat dan efisien.

Jika Anda sedang belajar Excel dan ingin mengetahui lebih jauh, lihat jalur keterampilan Excel Fundamentals untuk mempelajari semua hal penting. 

Apa itu XMATCH?

XMATCH adalah fungsi yang fleksibel dan andal untuk operasi pencocokan. XMATCH() memungkinkan Anda melakukan beragam operasi pencocokan, mulai dari pencarian vertikal dan horizontal hingga pencocokan tepat, mendekati, dan parsial. 

Bagi Anda yang telah lama menggunakan Excel, mungkin sudah akrab dengan fungsi MATCH. Sebelum pembaruan Excel yang memperkenalkan XMATCH, MATCH adalah fungsi andalan untuk tugas pencocokan. Anda dapat mempelajarinya di tutorial Excel Lookup Functions. 

Namun, seperti akan kita lihat nanti, MATCH bisa kurang memadai dalam skenario tertentu. Dalam hal ini, XMATCH dapat dianggap sebagai peningkatan yang ditunggu-tunggu dari MATCH tradisional, menyediakan pencarian ke segala arah dan beragam tipe pencocokan sehingga lebih mudah digunakan dan cocok untuk lebih banyak kasus penggunaan.

Perhatikan bahwa fungsi ini hanya tersedia di Excel untuk Microsoft 365 dan Excel 2021. Bagi pengguna versi Excel sebelumnya, MATCH masih menjadi satu-satunya fungsi yang tersedia.

Sekarang, mari kita lihat sintaks dan parameter XMATCH. 

Sintaks dan Parameter XMATCH

Berikut sintaks dasar fungsi XMATCH:

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Didukung oleh

Fungsi ini memiliki dua argumen wajib (yaitu lookup_value dan lookup_array) dan dua argumen opsional (match_mode dan search_mode) yang memberikan kemampuan untuk operasi yang lebih presisi dan fleksibel.

Di bawah ini, Anda dapat menemukan deskripsi keempat parameter tersebut:

• Lookup_value: Nilai yang ingin dicari.
• Lookup_array: Larik atau daftar tempat pencarian dilakukan. Dapat berupa larik pencarian vertikal atau horizontal.
• Match_mode: memungkinkan Anda memilih tipe pencocokan. Ada empat mode, antara lain:
• 0: Pencocokan tepat (mode bawaan)
• -1: Pencocokan tepat atau item terdekat yang lebih kecil
• 1: Pencocokan tepat atau item terdekat yang lebih besar
• 2: Pencocokan wildcard, seperti * dan ?.
• Search_mode: memungkinkan Anda menentukan mode pencarian, termasuk arah dan algoritme pencarian. Ada empat nilai yang mungkin:
• 1: Pencarian dari awal ke akhir (nilai bawaan)
• -1: Pencarian dari akhir ke awal (pencarian terbalik).
• 2:  Pencarian biner menaik. Pencarian dilakukan dengan asumsi larik pencarian diurutkan menaik, jika tidak, hasilnya tidak valid.
• -2:  Pencarian biner menurun. Pencarian dilakukan dengan asumsi larik pencarian diurutkan menurun, jika tidak, hasilnya tidak valid.

Mode Pencocokan: Presisi dan Fleksibilitas

XMATCH memiliki parameter opsional match_mode yang memungkinkan Anda memilih tipe pencocokan. Opsi-opsi ini sangat berguna, menyediakan cara untuk melakukan pencocokan yang presisi maupun mendekati. Mari kita analisis secara rinci mode pencocokan yang tersedia:

Pencocokan tepat

Pencocokan tepat (match_mode = 0) adalah mode bawaan XMATCH. Artinya, Excel akan menemukan kecocokan persis dari nilai pencarian dalam larik pencarian, jika tidak, akan mengembalikan kesalahan NA. Pencocokan tepat penting ketika akurasi dan presisi menjadi kunci, misalnya saat mencari entri tertentu dalam basis data.

Berikut contoh dasar cara menggunakan XMATCH untuk menemukan posisi tepat “Excel” dalam daftar teknologi yang tersedia di Katalog Kursus DataCamp. Kita memberikan nilai pencarian di D3 dan larik pencarian (A3:A14), dan Excel benar mengembalikan posisi 6. 

Pencocokan mendekati

Ada kalanya Anda perlu melakukan pencocokan yang lebih fleksibel, entah karena Anda tidak mengetahui nilai pencarian yang tepat atau karena Anda mencari hasil yang mendekati. 

Untungnya, XMATCH memiliki dua mode pencocokan yang memungkinkan Anda melakukan pencocokan tepat atau nilai terdekat yang lebih kecil, serta tepat atau nilai terdekat yang lebih besar. 

Pada contoh berikut, kita menggunakan pencocokan mendekati untuk menemukan jumlah kursus yang paling dekat dengan nilai pencarian 15. 

Saat menggunakan nilai terdekat yang lebih kecil (match_mode = -1), Excel mengembalikan posisi 6, karena 12 kursus adalah angka terdekat yang lebih kecil dalam daftar. Untuk nilai terdekat yang lebih besar, Excel mengembalikan 5, karena 16 kursus adalah angka terdekat yang lebih besar.

Kita juga menggunakan pencocokan tepat untuk menggambarkan apa yang terjadi ketika Excel tidak dapat menemukan kecocokan persis.

Pencocokan wildcard dan regex

Fitur inovatif di XMATCH adalah kemungkinan menggunakan wildcard dasar saat Anda menetapkan match_mode ke 2, serta ekspresi reguler yang lebih lanjut jika ditetapkan ke 3. Mode pencocokan ini hanya berfungsi dengan data teks.

XMATCH memungkinkan Anda menggunakan dua wildcard populer:

• ?: untuk mencocokkan satu karakter apa pun.
• *: untuk mencocokkan urutan karakter apa pun.

Di bawah ini, Anda dapat menemukan berbagai contoh cara menggunakan pencocokan regex. Pada contoh pertama, kita mencari teknologi yang dimulai dengan huruf E. Kedua, kita mencari teknologi yang memiliki huruf A dalam namanya. Terakhir, kita mencari teknologi dengan tiga huruf.

Perhatikan bahwa XMATCH akan selalu mengembalikan kemunculan pertama dalam larik pencarian, kecuali kita tentukan sebaliknya (lihat bagian berikutnya).

Jika Anda ingin menggunakan ekspresi reguler yang lebih kompleks, ubah match_mode menjadi 3. Ekspresi reguler, sering disingkat "regex" atau "regexp," adalah cara untuk mendefinisikan pola pencarian, yang dapat digunakan untuk berbagai tugas manipulasi teks seperti mencari, mengurai, dan/atau mengganti teks. 

Untuk detail lebih lanjut tentang pencocokan pola, Excel Regex Tutorial: Menguasai Pencocokan Pola dengan Ekspresi Reguler adalah tempat yang tepat untuk mulai.

Berikut contoh yang cukup sederhana: Kita ingin menemukan posisi kursus yang kodenya memuat tiga huruf di awal, tiga di akhir, dan dua angka di tengah (31).

Mode Pencarian XMATCH: Arah dan Efisiensi

XMATCH memperkenalkan parameter baru yang memungkinkan Anda mengontrol arah pencarian. Fitur ini sangat berguna saat Anda lebih tertarik menemukan kemunculan terakhir alih-alih yang pertama, misalnya untuk tugas tertentu dalam analisis deret waktu. 

Mari kita telusuri secara rinci berbagai mode pencarian.

Pencarian terbalik

Untuk melakukan pencarian terbalik, Anda dapat mengatur parameter search_mode ke -1. Misalnya, di bawah ini Anda dapat melihat cara menemukan kursus pertama dan terakhir yang diselesaikan oleh pengguna Peter.

Pencarian biner

Terakhir, parameter search_mode juga memungkinkan penggunaan algoritme pencarian biner untuk melakukan operasi pencocokan. Pencarian biner adalah algoritme pencarian yang bekerja dengan berulang kali membagi dua interval pencarian. Ini adalah salah satu algoritme pencarian paling efektif, tetapi memerlukan data masukan yang sudah diurutkan sebelumnya. 

XMATCH memungkinkan Anda memanfaatkan pencarian biner dengan larik yang diurutkan baik menaik (search_mode = 2) maupun menurun (search_mode = -2). Jika larik pencarian tidak diurutkan, fungsi akan menghasilkan kesalahan.

Penerapan dan Contoh Praktis XMATCH

XMATCH sangat cocok untuk berbagai tugas Excel, dari dasar hingga lanjutan. Pada bagian ini, kami membahas beberapa penerapan praktis yang paling menarik.

Pencarian dua dimensi dengan INDEX/XMATCH

Fungsi INDEX dan XMATCH dapat bekerja bersama untuk melakukan pencarian dua dimensi, yaitu pencarian pada baris dan kolom secara bersamaan. Dengan menggabungkan kedua fungsi ini, kita dapat membuat pencarian dinamis yang sangat berguna di bidang seperti analisis keuangan, di mana perbandingan variabel antar kategori sangat umum. 

Sebagai contoh, pada gambar di bawah, kita menggabungkan INDEX dan MATCH untuk mengekstrak jumlah pengguna yang terdaftar di kursus Excel selama Q2. INDEX menerima tiga argumen di sini: larik tempat nilai dicari, nomor baris, dan nomor kolom. Nilai untuk dua argumen terakhir diberikan oleh beberapa fungsi XMATCH.

 Jika kita ingin mengubah teknologi atau Kuartal yang dilihat, Anda hanya perlu mengganti nilai pencarian pada fungsi XMATCH, dan hasil INDEX akan ikut berubah.

INDEX/XMATCH bekerja dengan cara yang sangat mirip dengan INDEX/MATCH. Jika Anda ingin mengetahui duet kuat ini beserta contoh praktisnya, lihat cara INDEX MATCH Multiple Criteria di Excel.

Pencarian multikolom

Kegunaan hebat lainnya dari XMATCH adalah menggabungkan banyak kriteria untuk membuat pencarian multikolom. Ini mungkin tidak intuitif, karena XMATCH memerlukan nilai dan larik pencarian untuk bekerja. Namun kita dapat menggunakan logika Boolean untuk membuat larik sementara dalam rumus XMATCH yang berisi nol dan satu untuk merepresentasikan baris yang memenuhi kriteria yang Anda tentukan. 

Misalnya, Anda ingin menemukan teknologi pertama dalam daftar yang memiliki kurang dari 1000 pengguna terdaftar di setiap kuartal. Anda dapat menggabungkan kondisi dengan menggabungkan hasil di setiap kolom menggunakan simbol *. Excel akan menemukan, di balik layar, larik yang seluruhnya berisi angka satu, dalam kasus kita, Tableau, sehingga posisinya 5.

Penyaringan larik dinamis

XMATCH juga dapat bekerja bersama fungsi FILTER dan ISNUMBER untuk meningkatkan penyaringan larik dinamis.

Di bawah ini, Anda dapat melihat cara kerjanya. Kita akan menggunakan fungsi-fungsi ini untuk menyaring informasi kursus yang diselesaikan oleh pengguna berdasarkan nama pengguna. 

Pertama, kita menggunakan XMATCH untuk menghitung posisi relatif setiap pengguna di tabel berdasarkan larik pencarian berisi nama yang akan kita gunakan untuk memfilter. Ini akan memberikan tabel dengan posisi relatif setiap nama, termasuk nilai null untuk pengguna yang tidak termasuk dalam daftar filter.

Kemudian, kita mengonversi kolom dengan posisi relatif dan nilai null menjadi TRUE atau FALSE menggunakan fungsi ISNUMBER.

Terakhir, kita menggunakan FILTER untuk memilih semua kolom yang ingin ditampilkan, serta kolom dengan nilai TRUE dan FALSE untuk memfilter baris.

Menangani nilai null dengan ISNA

Saat XMATCH tidak menemukan kecocokan, fungsi ini akan mengembalikan kesalahan NA. Meskipun ini sudah informatif, Anda dapat memperindah hasil dan mencegah salah tafsir dengan menggunakan fungsi ISNA dan IF.

Bayangkan kita memiliki tabel berisi kursus yang diselesaikan oleh Sam dan Peter, dan kita ingin mengetahui kursus mana yang sama-sama mereka ambil. Jika kita hanya menggunakan XMATCH, Excel akan mengembalikan posisi relatif kursus yang sama, dan nilai null jika tidak. Jika kita meneruskan XMATCH sebagai argumen ISNA, fungsi akan mengembalikan TRUE atau FALSE. Kemudian, dengan menggunakan fungsi IF, kita dapat mengganti nilai boolean tersebut dengan pesan lain.

Pencarian biner untuk dataset besar

Kami telah menyebutkan bahwa XMATCH memungkinkan Anda menggunakan algoritme pencarian biner selama operasi pencocokan. Fitur ini dapat menjadi pengubah permainan saat menangani dataset besar. Dengan catatan larik pencarian telah diurutkan, memilih mesin pencarian biner dapat mempercepat operasi pencocokan Anda secara drastis.

XMATCH vs MATCH: Perbedaan Utama

Seperti telah disebutkan, XMATCH dirancang untuk menggantikan fungsi MATCH tradisional. Keduanya memiliki tujuan yang sama, namun XMATCH hadir dengan serangkaian kemampuan baru, membuatnya lebih kuat daripada MATCH. Di bawah ini, Anda dapat menemukan daftar perbedaan utama antara XMATCH dan MATCH:

Jebakan Umum dan Solusinya

Ada banyak potensi penggunaan XMATCH. Namun, penting untuk menyebutkan masalah umum yang mungkin Anda temui saat menggunakannya. Berikut daftar beberapa jebakan yang sering terjadi dan kiat untuk mengatasinya.

• Periksa tipe data: XMATCH akan berperilaku tidak terduga jika Anda menerapkannya pada rentang dengan tipe data yang salah. Oleh karena itu, sebelum menerapkan fungsi, pastikan Anda telah menetapkan tipe data yang tepat untuk kolom Anda.
• Konsistensi rentang: Meskipun XMATCH memungkinkan rentang pencarian horizontal dan vertikal, mencampurnya dapat menimbulkan masalah tak terduga dan isu kinerja. Jika memungkinkan, usahakan konsisten dan pilih salah satu, horizontal atau vertikal.
• Manfaatkan mode pencocokan: XMATCH memiliki berbagai mode pencocokan. Mode bawaan adalah pencocokan tepat, tetapi Anda juga dapat menyetel pencarian dengan menggunakan pencarian mendekati dan operasi regex.
• Lebih baik dengan larik terurut: Secara umum, kinerja XMATCH akan selalu lebih baik jika diterapkan pada larik yang terurut. Ini benar untuk fungsi bawaan, dan merupakan prasyarat wajib jika Anda ingin menggunakan pencarian biner. 
• Menangani kesalahan N/A:  Seperti telah disebutkan, XMATCH akan menghasilkan kesalahan NA jika tidak ada kecocokan dalam pencarian Anda. Cara terbaik untuk mengatasinya dan meningkatkan keterbacaan adalah dengan meneruskan hasilnya ke kombinasi fungsi IF dan ISNA, atau ke fungsi IFNA.

Pertimbangan Kinerja

XMATCH bukan satu-satunya fungsi untuk melakukan operasi pencarian di Excel. Misalnya, Anda dapat mempertimbangkan menggunakan VLOOKUP, HLOOKUP, atau bahkan fungsi lama seperti MATCH. Namun, soal kecepatan pencarian, XMATCH bisa dibilang yang tercepat, terutama jika diterapkan pada larik yang terurut, di mana Anda juga dapat memanfaatkan kekuatan pencarian biner.

Perlu juga disebutkan bahwa XMATCH dirancang sebagai rumus larik dinamis, artinya dapat mengembalikan larik dengan ukuran variabel tergantung data masukan. Rumus larik dinamis sangat cocok saat bekerja dengan dataset besar atau perhitungan yang kompleks. 

Kesimpulan

Selamat telah sampai di akhir. XMATCH adalah fungsi yang kuat, dan Anda tidak boleh melewatkannya dalam analisis data Excel Anda. Baik sebagai fungsi mandiri maupun digabungkan dengan fungsi Excel lain, XMATCH akan membawa operasi pencarian Anda ke level baru. 

Namun masih banyak hal lain untuk dipelajari di dunia Excel yang luar biasa. Lihat kursus, blog, dan tutorial khusus kami untuk menjadi ahli Excel:

• XLOOKUP() vs. VLOOKUP(): Perbandingan untuk Pengguna Excel
• Cheat Sheet Manipulasi Data di Excel 
• Excel | Kursus Menguasai Teknik Lanjutan
• Intermediate Power Query di Excel 
• Cheat Sheet Pintasan Excel 
• Cheat Sheet Rumus Excel 
• Cara INDEX MATCH Multiple Criteria di Excel 
• Data Wrangling dengan INDEX-MATCH di Spreadsheet 
• Excel Regex Tutorial: Menguasai Pencocokan Pola dengan Ekspresi Reguler