Rumus MATCH Excel: Fungsi dan Penggunaan

Jika ingin mendapatkan data pada posisi tertentu, gunakan fungsi LOOKUP, VLOOKUP, HLOOKUP, atau XLOOKUP.

Sebaliknya, untuk mendapatkan posisi suatu data, maka rumus MATCH Excel bisa jadi pilihan.

File: fungsi-match-excel.xlsx.

Apa Itu Fungsi MATCH Pada Excel?

Fungsi MATCH pada Excel adalah fungsi Excel untuk mendapatkan posisi data secara relatif terhadap array atau range di mana data tersebut berada.

Sebagai contoh, pencarian terhadap C menggunakan MATCH untuk array {“A”, “B”, “C”} akan menghasilkan 3. Nilai ini merupakan posisi C di dalam array tersebut.

Sintaks

MATCH(lookup_value; lookup_array; [match_type])

• lookup_value (wajib) adalah nilai yang dicari di dalam lookup_array. lookup_value dapat berupa nilai dengan tipe data apa pun, seperti teks, angka, tanggal, atau Boolean.
• lookup_array (wajib) adalah array atau range di mana lookup_value dicari. Range dapat berorientasi horisontal atau vertikal.
• match_type (opsional) adalah parameter yang menentukan tingkat kemiripan lookup_value dan nilai-nilai di dalam lookup_array. Parameter ini punya tiga opsi nilai, yaitu:
  • 1 mencari nilai yang sama dengan lookup_value pada data yang diurutkan secara ascending. Bila tidak ketemu, MATCH akan mencari nilai terbesar yang lebih kecil dari lookup_value.
  • 0 mencari nilai sama persis antara lookup_value. Dengan 0, data lookup_array tidak perlu diurutkan.
  • -1 mencari nilai yang sama dengan lookup_value pada data yang terurut secara descending. Bila tidak ketemu, MATCH akan mencari nilai terkecil yang lebih besar dari lookup_value.

Pencarian fungsi MATCH bersifat case insensitive dan akan mengembalikan pesan error #N/A bila tidak ada yang cocok.

Selain itu, MATCH juga melakukan pencarian dari awal data ke akhir. Bila menginginkan pencarian dari bagian akhir ke awal, pertimbangkan penggunaan XMATCH.

Bila menemukan kecocokan lebih dari satu, MATCH akan mengembalikan nilai pertama yang ditemui.

Cara Menggunakan Rumus MATCH Excel Bentuk Standar

=MATCH(E1; A2:A11)

Di atas adalah rumus MATCH dalam bentuk standar.

Rumus menyediakan nilai bagi dua parameter wajib, yaitu:

• E1adalah lookup_value, yaitu nilai yang akan dicari.
• A2:A11 sebagai lookup_array di mana lookup_value dicari.

Dalam bentuk hardcode, rumus bisa dituliskan sebagai berikut:

=MATCH("Sumatera Barat"; A2:A11)

Berdasarkan gambar, rumus mengembalikan 8. Nilai ini adalah posisi baris Sumatera Barat di range A2:A11.

Perhatian: Ingat, ya. Fungsi MATCH pada Excel mengembalikan posisi relatif data di dalam suatu array atau range, bukan posisi cell.

Perhatikan urutan data pada gambar di atas. Kami mengurutkan data secara ascending karena MATCH menggunakan nilai default 1 bagi match_type bila parameter ini tidak disertakan. Nilai ini mensyaratkan agar data terurut secara ascending.

Rumus MATCH Array Excel

Parameter lookup_array fungsi MATCH pada Excel juga menerima bentuk array, tidak hanya range.

=MATCH("Bengkulu"; {"Aceh", "Bengkulu", "Jambi", "Lampung", "Riau"})

Rumus di atas akan mengembalikan 2, yang merupakan posisi Bengkulu di dalam array.

Exact dan Approximate MATCH Excel

Approximate atau exact search di MATCH dilakukan dengan mengubah nilai parameter match_type.

Exact search adalah pencarian yang mencari kecocokan mutlak antara lookup_value dan nilai-nilai di dalam lookup_array dan dilakukan dengan memberikan nilai 0 bagi match_type.

Pada Gambar 03 di bawah, rumus-rumus mencari padanan nilai PDRB.

Karena menggunakan match_type 0, maka hanya pencarian terhadap 275,640,000 yang memberikan hasil. Selebihnya mengembalikan #N/A karena tidak ada nilai 370,000,000 atau 260,000,000 di dalam range A2:A11.

Approximate search pada rumus MATCH Excel terbagi jadi dua, yaitu 1 dan -1.

Nilai match_type 1 mencari nilai sama persis dengan lookup_value pada data terurut ascending. Kalau tidak ada yang cocok, MATCH akan mencari nilai terbesar yang lebih kecil dari nilai yang dicari. Bila gak ketemu juga, pesan error #N/A akan tampil.

Berdasarkan gambar di bawah, tidak ada PDRB 50,000,000 atau yang lebih kecil dari nilai tersebut (karena nilai terkecil dalam data adalah 79,580,000). Konsekuensinya, rumus menampilkan #N/A.

80,000,000 juga tidak ada di dalam data. Tapi, ada nilai terbesar yang lebih kecil dari nilai tersebut, yaitu nilai pada cell B2.

-1 akan mencari nilai sama persis dengan lookup_value pada data yang terurut secara descending. Kalau gak ketemu, MATCH akan mencari dan mengembalikan posisi nilai terkecil yang lebih besar dari lookup_value. Bila masih gak ketemu lagi, pesan error #N/A muncul.

Pencarian terhadap PDRB 880,000,000 mengembalikan #N/A karena tidak ada nilai terkecil yang lebih besar dari nilai tersebut (paling besar adalah 859,870,000).

850,000,000 memberikan nilai 1 karena, walau pun tidak ada di dalam data, tapi ada nilai terkecil yang lebih besar dari pada nilai ini, yaitu 859,870,000.

Mencari Sebagian Kata dengan MATCH Wildcard Excel

Pencarian dengan wildcard digunakan saat kita cuma tahu sepenggal dari keseluruhan teks yang ingin dicari. Contohnya, kita ingat nama provinsi berakhiran huruf u, tapi tidak ingat apakah Riau, Kepulauan Riau, atau Bengkulu.

Ada tiga wildcard yang bisa digunakan di rumus MATCH Excel, yaitu:

• Tanda asterisk (*) mewakili satu atau lebih karakter dari suatu teks. Contoh: Kriteria *jawa* terhadap nama-nama provinsi di Indonesia menghasilkan Jawa Barat, Jawa Timur, atau Jawa Tengah.
• Tanda tanya (?) mewakili satu karakter dari suatu teks. Contoh: Kriteria ?i* terhadap nama-nama negara di Asia Tenggara menghasilkan Singapura, Timor Leste, dan Vietnam.
• Tanda tilde (~) berfungsi untuk menghilangkan kemampuan satu tanda asterisk atau tanda tanya yang ditulis segera setelahnya.

Seperti teks hardcode, kriteria wildcard perlu diapit oleh tanda kutip ganda lurus.

Perlu diingat bahwa walau pun ada beberapa item data yang cocok dengan kriteria wildcard, MATCH hanya akan mengembalikan data yang pertama kali ditemui.

Rumus	Keterangan	Data Cocok
=MATCH("*"; A2:A11; 0)	Mencari teks yang mana pun juga.	A2 dan A3:A11.
=MATCH("*au"; A2:A11; 0)	Mencari teks dengan akhiran au.	A3, A6.
=MATCH("*a *"; A2:A11; 0)	Mencari teks yang salah satu katanya diakhiri dengan huruf a dan diikuti dengan spasi.	A2, A4, A7, A10.
=MATCH("b*u*"; A2:A11; 0)	Mencari teks yang diawali dengan b dan memiliki teks di antara huruf tersebut dan u.	A10, A11.
=MATCH("?"; A2:A11; 0)	Mencari teks yang hanya terdiri dari satu karakter.	Tidak ada.
=MATCH("?am*"; A2:A11; 0)	Mencari teks yang memiliki satu karakter di depan am.	A5, A8.
=MATCH("*~?*"; A2:A11; 0)	Mencari teks yang memiliki tanda tanya.	A7, A11.
=MATCH("*~**"; A2:A11; 0)	Mencari teks yang memiliki tanda asterisk.	A9, A11.
=MATCH("*~*~?*"; A2:A11; 0)	Mencari teks yang memiliki tanda asterisk dan tanda tanya ditulis secara berurutan.	A11.

Rumus VLOOKUP dan MATCH

Secara standar, fungsi VLOOKUP punya satu kekurangan, yaitu indeks kolom nilai kembalian perlu ditulis secara hardcode.

Saat struktur tabel berubah, baik kolom bertambah atau berkurang, ada kemungkinan nilai indeks tidak lagi relevan dengan posisi yang sebenarnya.

=VLOOKUP(G2; A2:D11; 3; FALSE)

Rumus di atas mencari PDRB Sumatera Selatan untuk tahun 2022.

Pada gambar, terlihat bahwa tahun-tahun PDRB terdiri dari 2021, 2022, dan 2023. Seandainya kita ingin menambahkan PDRB tahun 2020, nilai indeks 3 jadi gak valid lagi (karena mengacu ke 2021).

Dengan kombinasi rumus VLOOKUP dan MATCH, indeks kolom lebih fleksibel.

=VLOOKUP(G2; A2:D11; MATCH(G3; A1:D1; 0); FALSE)

Rumus di atas menggantikan 3 dengan MATCH(G3; A1:D1; 0).

MATCH(G3; A1:D1; 0) mencari tahun 2022 (cell G3) di range header A1:D1 dan mengembalikan posisi cell tahun tersebut relatif terhadap seluruh cell header.

Bila di kemudian waktu ada penambahan atau pengurangan kolom yang menyebabkan kolom 2022 bergeser, maka rumus tetap aman. Sebabnya, rumus MATCH Excel menjadikan posisi 2022 menjadi relatif (karena berdasarkan rumus) dan tidak baku (karena ditulis 3).

Rumus HLOOKUP MATCH

Fungsi HLOOKUP punya kelemahan yang sama dengan VLOOKUP, yaitu posisi indeks baris yang beresiko invalid saat terjadi penambahan atau pengurangan baris.

Rumus di bawah mencari PDRB tahun 2021 untuk Riau, yang terletak pada baris ketujuh array.

=HLOOKUP(G3; B1:D11; 7; FALSE)

Menggantikan nilai 7 dengan rumus MATCH membuat parameter indeks baris lebih luwes dan mengurangi resiko salah indeks.

=HLOOKUP(G3; B1:D11; MATCH(G2; A1:A11; 0); FALSE)

Rumus MATCH(G2; A1:A11; 0) mencari baris Riau di range A1:A11 dan memberikan posisi tersebut sebagai indeks nilai kembalian bagi HLOOKUP.

Fungsi MATCH dan INDEX

Fungsi INDEX mungkin merupakan fungsi yang paling sering dikombinasikan dengan fungsi MATCH.

=INDEX(A1:D11; 5; 4)

Rumus INDEX di atas mencari PDRB Jambi (baris kelima range A1:D11) untuk tahun 2023 (kolom keempat range yang sama).

Kita sudah tau bahwa hardcode nilai bukan hal baik.

MATCH mampu menggantikan 5 dan 4 agar rumus lebih kebal terhadap perubahan.

=INDEX(A1:D11; MATCH(G2; A1:A11; 0); MATCH(G3; A1:D1; 0))

MATCH(G2; A1:A11; 0) mencari posisi baris Jambi di A1:A11. Sementara itu, MATCH(G3; A1:D1; 0) mengembalikan posisi kolom 2023 pada range yang sama.

Secara nilai, kedua MATCH masing-masing menghasilkan 5 dan 4. Bedanya, rumus lebih luwes dalam menghadapi perubahan struktur tabel.

Rumus IF dan MATCH

Fungi IF adalah fungsi pengendali keputusan dan dapat digunakan bersamaan dengan MATCH. Berikut salah satu contoh kombinasi rumus bagi kedua fungsi.

=IF(ISNA(MATCH(D2; $A$2:$A$11; 0)); "Tidak Ada"; "Ada")

Potongan rumus MATCH(D2; $A$2:$A$11; 0) melihat apakah Kepulauan Riau (D2) ada di dalam range A2:A11 dan mengembalikan posisi provinsi tersebut.

Fungsi ISNA kemudian melihat apakah nilai yang dikembalikan MATCH adalah pesan error #N/A atau bukan. Bila ya, maka ISNA mengembalikan TRUE. Sebaliknya, FALSE.

Fungsi IF mengevaluasi hasil ISNA. Bila TRUE (yang artinya hasil MATCH adalah pesan error #N/A) maka IF mengembalikan teks Tidak Ada. Bila FALSE (yang artinya MATCH mengembalikan angka posisi baris), maka IF mengembalikan Ada.