Anda dapat menggunakan fungsi Excel MATCH untuk menemukan posisi relatif dari nilai tertentu dalam rentang sel atau larik.
Fungsi MATCH mirip dengan fungsi VLOOKUP karena keduanya dikategorikan dalam Fungsi Pencarian/Referensi Excel. VLOOKUP mencari nilai tertentu dalam kolom dan mengembalikan nilai di baris yang sama sementara fungsi MATCH mencari nilai tertentu dalam rentang dan mengembalikan posisi nilai tersebut.
Fungsi Excel MATCH mencari nilai tertentu dalam rentang sel atau larik dan mengembalikan posisi relatif dari tampilan pertama nilai tersebut dalam rentang tersebut. Fungsi MATCH juga dapat digunakan untuk mencari nilai tertentu dan mengembalikan nilai yang sesuai dengan bantuan fungsi INDEX (seperti Vlookup). Mari kita lihat cara menggunakan fungsi Excel MATCH untuk menemukan posisi nilai pencarian dalam rentang sel.
Fungsi PERTANDINGAN Excel
Fungsi MATCH adalah fungsi bawaan di Excel dan terutama digunakan untuk menemukan posisi relatif dari nilai pencarian dalam kolom atau baris.
Sintaks Fungsi MATCH:
=MATCH(nilai_pencarian,larik_pencarian,[jenis_kecocokan})Di mana:
nilai lookup - Nilai yang ingin Anda cari dalam rentang sel atau larik tertentu. Ini bisa berupa nilai numerik, nilai teks, nilai logika, atau referensi sel yang memiliki nilai.
lookup_array – Array sel tempat Anda mencari nilai. Itu harus satu kolom atau satu baris.
match_type – Ini adalah parameter opsional yang dapat diatur ke 0,1, atau -1 dan defaultnya adalah 1.
- 0 mencari kecocokan persis, ketika tidak ditemukan, mengembalikan kesalahan.
- -1 mencari nilai terkecil yang lebih besar dari atau sama dengan lookup_value ketika array pencarian dalam urutan menaik.
- 1 mencari nilai terbesar yang kurang dari atau sama dengan nilai look_up ketika array pencarian dalam urutan menurun.
Temukan Posisi Pertandingan yang Tepat
Mari kita asumsikan, kita memiliki kumpulan data berikut di mana kita ingin menemukan posisi nilai tertentu.
Pada tabel ini, kita ingin mencari posisi nama kota (Memphis) pada kolom (A2:A23), jadi kita menggunakan rumus ini:
=MATCH("memphis",A2:A23,0)Argumen ketiga diatur ke '0' karena kami ingin menemukan nama kota yang sama persis. Seperti yang Anda lihat, nama kota “memphis” dalam rumus menggunakan huruf kecil sedangkan pada tabel huruf pertama nama kota menggunakan huruf besar (Memphis). Namun, rumus tersebut dapat menemukan posisi nilai yang ditentukan dalam rentang yang diberikan. Itu karena fungsi MATCH tidak peka huruf besar-kecil.
Catatan: Jika nilai_pencarian tidak ditemukan dalam rentang pencarian atau jika Anda menentukan rentang pencarian yang salah, fungsi akan mengembalikan kesalahan #N/A.
Anda dapat menggunakan referensi sel dalam argumen pertama fungsi alih-alih nilai langsung. Rumus di bawah ini menemukan posisi nilai di sel F2 dan mengembalikan hasilnya di sel F3.
Temukan Posisi Perkiraan Pertandingan
Ada dua cara Anda dapat mencari perkiraan atau kecocokan persis dari nilai pencarian dan mengembalikan posisinya.
- Salah satu caranya adalah dengan mencari nilai terkecil yang lebih besar atau sama (kecocokan terbesar berikutnya) dengan nilai yang ditentukan. Ini dapat dicapai dengan mengatur argumen terakhir (match_type) dari fungsi sebagai '-1'
- Cara lain adalah nilai terbesar yang kurang dari atau sama (kecocokan terkecil berikutnya) dengan nilai yang diberikan. Ini dapat dicapai dengan mengatur match_type fungsi sebagai '1'
Pertandingan Terkecil Berikutnya
Jika fungsi tidak dapat menemukan kecocokan yang tepat dengan nilai yang ditentukan saat jenis pencocokan diatur ke '1', fungsi tersebut akan menempatkan nilai terbesar yang sedikit kurang dari nilai yang ditentukan (yang berarti nilai terkecil berikutnya) dan mengembalikan posisinya . Agar ini berfungsi, Anda perlu mengurutkan array dalam urutan menaik, jika tidak, itu akan menghasilkan kesalahan.
Dalam contoh, kami menggunakan rumus di bawah ini untuk menemukan kecocokan terkecil berikutnya:
=MATCH(F2,D2:D23,1)Ketika rumus ini tidak dapat menemukan kecocokan yang tepat untuk nilai di sel F2, rumus ini menunjuk ke posisi (16) dari nilai terkecil berikutnya, yaitu 98.
Pertandingan Terbesar Berikutnya
Ketika jenis pencocokan diatur ke '-1' dan fungsi MATCH tidak dapat menemukan kecocokan yang tepat, ia menemukan nilai terkecil yang lebih besar dari nilai yang ditentukan (yang berarti nilai terbesar berikutnya) dan mengembalikan posisinya. Array pencarian harus diurutkan dalam urutan menurun untuk metode ini jika tidak maka akan mengembalikan kesalahan.
Misalnya, masukkan rumus berikut untuk menemukan kecocokan terbesar berikutnya dengan nilai pencarian:
=MATCH(F2,D2:D23,-1)Fungsi MATCH ini mencari nilai dalam F2 (55) dalam rentang pencarian D2:D23, dan ketika tidak dapat menemukan kecocokan yang tepat, ia mengembalikan posisi (16) dari nilai terbesar berikutnya, yaitu 58.
Pertandingan Wildcard
Karakter pengganti dapat digunakan dalam fungsi MATCH hanya ketika match_type diatur ke '0' dan nilai pencarian adalah string teks. Ada wildcard yang dapat Anda gunakan dalam fungsi MATCH: tanda bintang (*) dan tanda tanya (?).
- Tanda tanya (?) digunakan untuk mencocokkan setiap karakter atau huruf dengan string teks.
- Tanda bintang (*) digunakan untuk mencocokkan sejumlah karakter dengan string.
Misalnya, kami menggunakan dua karakter pengganti '?' dalam nilai_pencarian (Lo??n) dari fungsi MATCH untuk menemukan nilai yang cocok dengan string teks dengan dua karakter mana pun (di tempat karakter pengganti). Dan fungsi mengembalikan posisi relatif dari nilai yang cocok di sel E5.
=MATCH("Lo??n",A2:A22,0)
Anda dapat menggunakan karakter pengganti (*) dengan cara yang sama seperti (?), tetapi tanda bintang digunakan untuk mencocokkan sejumlah karakter sementara tanda tanya digunakan untuk mencocokkan karakter tunggal apa pun.
Misalnya, jika Anda menggunakan 'sp*', fungsinya bisa cocok dengan speaker, kecepatan, atau spielberg, dll. Tetapi jika fungsi menemukan beberapa/duplikat nilai yang cocok dengan nilai pencarian, itu hanya akan mengembalikan posisi nilai pertama.
Dalam contoh, kami memasukkan "Kil*o" dalam argumen lookup_value. Jadi fungsi MATCH() mencari teks yang berisi 'Kil' di awal, 'o' di akhir, dan sejumlah karakter di antaranya. 'Kil*o' cocok dengan Kilimanjaro dalam larik dan oleh karena itu fungsinya mengembalikan posisi relatif Kilimanjaro, yaitu 16.
INDEKS dan MATCH
Fungsi MATCH jarang digunakan sendiri. Mereka sering dipasangkan dengan fungsi lain untuk membuat formula yang kuat. Ketika fungsi MATCH digabungkan dengan fungsi INDEX, ia dapat melakukan pencarian tingkat lanjut. Banyak orang masih lebih suka menggunakan VLOOKUP untuk mencari nilai, karena lebih sederhana tetapi INDEX MATCH lebih fleksibel dan lebih cepat daripada VLOOKUP.
VLOOKUP hanya dapat mencari nilai secara vertikal yaitu kolom sementara INDEX MATCH combo dapat melakukan pencarian vertikal dan horizontal.
Fungsi INDEX digunakan untuk mengambil nilai di lokasi tertentu dalam tabel atau rentang. Fungsi MATCH mengembalikan posisi relatif suatu nilai dalam kolom atau baris. Saat digabungkan, MATCH menemukan nomor baris atau kolom (lokasi) dari nilai tertentu, dan fungsi INDEX mengambil nilai berdasarkan nomor baris dan kolom tersebut.
Sintaks fungsi INDEX:
=INDEX(array,baris_num,[kol_num],)Pokoknya mari kita lihat bagaimana INDEX MATCH bekerja dengan sebuah contoh.
Pada contoh di bawah ini, kami ingin mengambil skor 'Quiz2' untuk siswa 'Anne'. Untuk melakukan itu kita akan menggunakan rumus di bawah ini:
=INDEX(B2:F20,MATCH(H2,A2:A20,0),3)INDEX membutuhkan nomor baris dan kolom untuk mengambil nilai. Dalam rumus di atas, fungsi MATCH bersarang menemukan nomor baris (posisi) dari nilai 'Anne' (H2). Kemudian kami menyediakan nomor baris itu ke fungsi INDEX dengan rentang B2:F20 dan nomor kolom (3), yang kami tentukan. Dan fungsi INDEX mengembalikan skor '91'.
Pencarian dua arah dengan INDEX dan MATCH
Anda juga dapat menggunakan fungsi INDEX dan MATCH untuk mencari nilai dalam rentang dua dimensi (pencarian dua arah). Dalam contoh di atas, kami menggunakan fungsi MATCH untuk menemukan nomor baris suatu nilai, tetapi kami memasukkan nomor kolom secara manual. Tetapi kita dapat menemukan baris dan kolom dengan menyarangkan dua fungsi MATCH, satu dalam argumen row_num dan satu lagi dalam argumen column_num dari fungsi INDEX.
Gunakan rumus ini untuk pencarian dua arah dengan INDEX dan MATCH:
=INDEX(A1:F20,MATCH(H2,A2:A20,0),MATCH(H3,A1:F1,0))Seperti yang kita ketahui, fungsi MATCH dapat mencari suatu nilai baik secara horizontal maupun vertikal. Dalam rumus ini, fungsi MATCH kedua dalam argumen colum_num menemukan posisi Quiz2 (4) dan memasoknya ke fungsi INDEX. Dan INDEX mengambil skor.
Sekarang, Anda tahu cara menggunakan fungsi Match di Excel.