Fungsi Match - Kegunaan Dan Teladan Rumus
Pada kesempatan ini juga akan diperlihatkan bagaimana cara memakai kombinasi MATCH dengan VLOOKUP, HLOOKUP dan INDEX untuk membangun formula lookup yang dinamis.
- Syntax dan Cara Penggunaan Fungsi MATCH
- Contoh-Contoh Rumus Excel MATCH
- Rumus MATCH Menggunakan Karakter Wildcard
- Rumus MATCH Yang Bersifat Case Sensitive
- Membandingkan Dua Kolom Untuk Melihat Kesamaan dan Perbedaan
- Kombinasi Rumus Excel VLOOKUP dan MATCH
- Kombinasi Rumus Excel HLOOKUP dan MATCH
- Kombinasi Rumus Excel INDEX dan MATCH
Link Download Contoh Rumus : Excel 2007 - MATCH
Syntax dan Cara Penggunaan Fungsi MATCH
Fungsi MATCH merupakan salah satu komponen rumus excel yang sanggup dipakai untuk melaksanakan lookup, atau pencarian data/referensi.
Fungsi ini bekerja dengan cara mencari posisi relative sebuah nilai dalam range atau array dan menghasilkan bilangan yang merupakan index posisi relative sel yang berisi nilai yang dicari tersebut.
Adapun cara memakai fungsi Excel MATCH harus mengikuti hukum syntax berikut:
MATCH(lookup_value, lookup_array, [match_type])
Lookup_value (diperlukan) : Merupakan nilai yang ingin anda temukan, sanggup berupa bilangan , text, maupun nilai logika. Nilai ini sanggup diketikan eksklusif dalam rumus, maupun sanggup ditempatkan pada sebuah sel referensi.
Lookup_array (diperlukan) : tabel atau array dimana dilakukan pencarian data
Match_type (opsional) = Menentukan metode pencarian, berupa isyarat angka -1, 0 atau 1. Meskipun bersifat opsional, namun argumen fungsi ini sangat penting.
- match_type = -1: Menemukan nilai terendah dalam lookup_array yang lebih besar atau sama dengan nilai yang dicari. Data dalam lookup_array harus disusun dari tertinggi ke terendah, atau Z-A
- match_type = 0 : Menemukan nilai pertama dalam lookup_array yang persis sama nilainya dengan nilai yang dicari.
- match_type = 1 : Digunakan untuk menemukan nilai terbesar dalam lookup_array yang lebih kecil atau sama dengan nilai yang dicari. Memerlukan penyusunan array dari terkecil ke terbesar atau A – Z. Opsi ini bersifat default, artinya kalau argumen match_type dikosongkan, maka fungsi MATCH akan memakai type 1 dalam melaksanakan pencarian data.
Untuk memudahkan pemahaman mengenai fungsi excel MATCH, mari kita menciptakan sebuah formula sederhana yang memakai data sebagai berikut:
- Kolom A (range A2:A7) berisi nama karyawan
- Kolom B (range B2:B7) berisi pencapaian sasaran penjualan yang diraih oleh masing-masing karyawan.
Untuk mendapat posisi relatif salah seorang karyawan (misalnya si Budi) dalam list nama karyawan maka sanggup memakai rumus berikut:
=MATCH("Budi",A2:A7,0)
Kita juga sanggup menempatkan text Budi dalam sebuah rujukan sel, anggaplah sel B13, sehingga rumusnya menjadi ibarat di bawah ini:
=MATCH(B13,A2:A7,0)
Perhatikan penggunaan angka 0 sebagai argumen match_type.
Type 0 dipakai lantaran list nama tidak disusun dari A-Z ataupun Z-A, dan kita menginginkan hasil pencarian posisi untuk nama yang persis sama dengan nama yang dicari.
Dan hasilnya yaitu si “Budi” berada pada nomor urut ke-3
4 Hal Yang Perlu Diketahui Mengenai Fungsi Excel MATCH
Cara memakai fungsi MATCH memang sangat mudah, akan tetapi ada beberapa hal yang perlu diketahui dan diperhatikan terkait penggunaan rumus ini, yaitu:
- Fungsi MATCH mengembalikan nilai posisi relative dari nilai yang dicari dalam array, bukan nilai itu sendiri ibarat yang sanggup dilakukan oleh rumus VLOOKUP.
- Fungsi MATCH tidak bersifat case sensitive sehingga tidak membedakan penggunaan karakter kecil, ataupun karakter besar
- Jika ada lebih dari 1 nilai yang sama dalam lookup_array, maka fungsi MATCH hanya akan memperlihatkan informasi posisi relative nilai yang pertama (paling atas atau paling kiri) dalam range/array.
- Jika nilai lookup_value tidak dijumpai dalam lookup_aray maka rumus akan menghasilkan nilai error #N/A
Contoh-Contoh Rumus Excel Bagaimana Menggunakan Fungsi MATCH
Sebelum menginjak pada cuilan ini, diharapkan anda sudah mengenal basic dan cara penggunaan fundamental dari fungsi MATCH sesuai pembahasan di atas. Jika sudah, selanjutnya mari kita gali lebih dalam kegunaan rumus MATCH melalui contoh-contoh dibawah ini:
Rumus Excel MATCH Dengan Karakter Wildcard
Seperti halnya beberapa fungsi excel lainnya, MATCH juga sanggup mengenali beberapa karakter wildcard berikut:
• Tanda Tanya (?) – mewakili karakter tunggal
• Tanda bintang atau asterisk (*) – mewakili sekumpulan karakter.
Catatan: Karakter wildcard hanya sanggup dipakai dalam Rumus MATCH yang memakai match_type = 0
Formula MATCH dengan karakter wilcard mempunyai kegunaan dalam beberapa situasi dimana kita ingin menemukan sel berisi text yang tidak perlu persis sama dengan text yang dicari, tetapi ada kemiripan.
Untuk menjelaskan hal di atas, mari kita perhatikan pola kasus berikut:
Anggaplah anda mempunyai list nama karyawan di kolom A. Anda ingat bahwa ada seorang karyawan yang tidak masuk kerja di bulan lalu, tetapi, anda tidak ingat namanya, tetapi anda ingat sedikit frase text yang merupakan cuilan depan dari cuilan namanya, contohnya anda ingat “bu”.
Misalnya nama karyawan disimpan di range B2:B7, maka rumus untuk mendapat posisi nama karyawan yang mengandung text “bu” yaitu sebagai berikut:
=MATCH("bu*",B2:B7,0)
Contoh rumus diatas memang nampaknya hanyalah rumus maen-maen sehingga tidak begitu nampak manfaat nya. Karena dengan sepintas mata saja kita sanggup mengetahui posisisi budi dalam range.
Ya memang pola tersebut hanya sebagai citra saja. Manfaat bekerjsama akan terasa kalau kita bekerja dengan data yang banyak, ratusan hingga ribuan baris.
Untuk mengakibatkan rumus lebih fleksibel, maka alangkah baiknya cuilan text yang dicari tadi ditempatkan pada sebuah rujukan sel.
Kemudian rumus kita modifikasi dengan menggabungkan karakter wildcard dengan sel rujukan memakai sumbangan rumus penggabung kata, sanggup memakai fungsi CONCATENATE, ataupun memakai operator ampersand (&)
Untuk mewakili hanya satu karakter, maka kita sanggup memakai tanda tanya (?) sebagai karakter wilcard.
Misalnya kita lupa apakah nama orang yang dicari iwan atau ivan. Maka kita sanggup menciptakan rumus berikut untuk mencari posisi relativenya.
=MATCH("i?an",B2:B7,0)
Formula tersebut akan menemukan posisi relatif si Iwan dan mengembalikan nilai 4.
Formula MATCH dengan Case Sensitive
Sebagaimana sudah dijelaskan sebelumnya bahwa fungsi MATCH tidak membedakan karakter kecil dan karakter besar.
Namun, bukan berarti kita tidak sanggup melaksanakan Lookup secara case sensitive.
Nah, untuk menjadikannya case sensitive maka kita sanggup mengkombinasikan fungsi MATCH dengan fungsi EXACT.
Perhatikan struktur formula berikut:
MATCH(TRUE, EXACT(lookup_array, lookup_value), 0)
Formula tersebut mengerjakan tugasnya dengan budi sebagai berikut:
- Fungsi EXACT dipakai untuk membandingkan lookup_value dengan masing-masing elemen pada lookup_array.
- Jika nilai dalam sel yang dibandingkan sama persis, maka fungsi menghasilkan nilai TRUE, jika sebaliknya maka menghasilkan nilai FALSE.
- Kemudian, fungsi MATCH mengambil tugasnya, membandingkan TRUE dengan nilai dalam array yang dihasilkan oleh rumus EXACT.
- Pada akhirnya, rumus MATCH memperlihatkan informasi posisi relative nilai TRUE pertama dalam array yang dihasilkan oleh rumus EXACT.
Perlu diperhatikan kembali bahwa lantaran ini merupakan rumus array, maka anda harus menekan Ctrl + Shift + Enter supaya rumus tersebut sanggup bekerja
Asumsikan bahwa lookup_value yaitu dalam sel E1, dan kemudian lookup_array yaitu range A2:A9, maka formula sanggup dituliskan sebagai berikut:
=MATCH(TRUE, EXACT(A2:A9,E1),0)
Screenshoot di bawah ini memperlihatkan bagaimana formula MATCH EXACT sanggup bekerja mencari posisi relative data sel secara case sensitive.
Membandingkan 2 Kolom Untuk Kesamaan dan Perbedaan (ISNA MATCH)
Mengecek dua buah kolom untuk melihat kesamaan dan perbedaan data didalamnya merupakan salah satu skill yang cukup bermanfaat ketika bekerja memakai microsoft excel.
Ada beberapa metode yang sanggup dilakukan untuk melaksanakan kiprah ini. Salah satunya yaitu memakai formula ISNA/MATCH.
Fungsi ISNA dipakai untuk mengecek sel apakah bernilai error #N/A dan mengembalikan nilai TRUE kalau sel berisi nilai error #N/A.
Anggaplah kita mempunyai list utama pada kolom A dan list data yang akan dicek pada kolom B.
Bagaimana cara mengecek apakah data dalam list di kolom B ada atau tidak ada di list pada kolom A?
Berikut yaitu pola rumus yang sanggup dipakai untuk kiprah tersebut.
Membandingkan dua kolom dengan mengabaikan karakter besar/kecil (case insensitive)
=IF(ISNA(MATCH(B2,A:A,0)),"Tidak ada di List","")
Membandingkan dua kolom memperhatikan karakter besar/kecil (case sensitive)
=IF(ISNA(MATCH(TRUE,EXACT(A:A,B2),0)),"Tidak Ada di List","")
Kombinasi VLOOKUP dan MATCH
Pada cuilan ini diasumsikan bahwa anda sudah memahami cara memakai fungsi excel VLOOKUP. Fungsi ini merupakan salah satu fungsi excel yang sangat diajurkan untuk dikuasai.
Salah satu kelemahan rumus VLOOKUP yang cukup mengganggu yaitu dimana fungsi ini tidak lagi memperlihatkan hasil yang relevan ketika ada kolom yang dihapus atau disisipkan pada tabel lookup.
Hal tersebut disebabkan fungsi VLOOKUP memakai col_index yang bersifat statis untuk memilih urutan kolom tertentu.
Fungsi MATCH tiba sebagai solusi atas permasalahan ini. Alih-alih memakai angka statis sebagai col_index dalam rumus VLOOKUP, rumus MATCH sanggup menyediakan col_index yang bersifat dinamis, sehingga tidak perlu khawatir rumus VLOOKUP akan error atau tidak bekerja ketika ada kolom dalam tabel lookup yang dihapus.
Untuk membantu pemahaman atas klarifikasi diatas, mari kita berguru dari studi kasus berikut:
Perhatikan kembali pola tabel pertama yang dipakai dalam tutorial ini yaitu tabel berisi list nama karyawan di kolom A, dan data pencapaian sasaran di kolom B.
Anggaplah kita ingin mengetahui berapa persen pencapaian sasaran salah satu karyawan.
Untuk memudahkan pengecekan satu persatu, nama karyawan tidak diketikan eksklusif pada formula, melainkan diketikan pada sebuah rujukan sel, contohnya sel B10.
Maka kita sanggup menciptakan formula ibarat dibawah ini.
=VLOOKUP(B10,A2:B7,2,0)
Selanjutnya mari kita lihat kelemahan rumus tersebut.
Sisipkan sebuah kolom dengan cara insert column antara kolom A dan B.
Anda sanggup mengamati, ternyata rumus vlookup tidak lagi memperlihatkan hasil yang relevan setelah dilakukan penyisipan kolom antara kolom A dan B.
Apa sebab?
Penyebab dilema ini yaitu penggunaan argumen col_index yang bersifat statis, yaitu angka 2. Sehingga rumus VLOOKUP akan membaca hasil pada kolom ke-2 dalam tabel lookup. Sementara kolom ke-2 ini tidak berisi data atau kosong sesudah dilakukan insert column.
Solusinya:
Sediakan col_index yang bersifat dinamis.
Cara untuk mendapat angka angka col_index yaitu dengan memakai fungsi MATCH.
Cara untuk mendapat angka angka col_index yaitu dengan memakai fungsi MATCH.
Anggaplah nama karyawan yang akan di cek ditempatkan pada sel C10, kemudian label "Pencapaian" disimpan di sel A11.
Kita tahu bahwa untuk mendapat nilai pencapaian sanggup memakai rumus VLOOKUP yaitu:
=VLOOKUP(C10,A2:B7,2,0)
Perhatikan : argumen col_index = 2, argumen ini sanggup kita ganti dengan fungsi match
=MATCH("pencapaian",,A1:B1,0)
Atau memakai rujukan sel:
=MATCH(A11,A1:B1,0)
Fungsi MATCH tersebut dipakai untuk memperoleh posisi relative kolom “Pencapaian” yang juga sebagai col_index dalam fungsi VLOOKUP.
Sehingga rumus VLOOKUP sanggup dituliskan sebagai berikut:
=VLOOKUP(C10,A2:B7,MATCH(A11,A1:B1,0),0)
Untuk lebih jelasnya, perhatikan screenshot dibawah ini.
Sekarangnya saatnya kita test:
Lakukan insert column diantara kolom A dan B, dan perhatikan hasilnya.
Kita akan melihat bahwa rumus VLOOKUP tetap menghasilkan nilai yang benar. Hal ini lantaran col_index akan berubah secara dinamis mengikuti posisi relatif kolom pencapaian dalam tabel lookup.
Kombinasi HLOOKUP dan MATCH
Dengan cara yang serupa pada rumus VLOOKUP, kita juga sanggup memakai fungsi MATCH untuk memperbaki kinerja rumus HLOOKUP.
Jika pada rumus VLOOKUP, fungsi MATCH ini dipakai untuk memperoleh col_index, maka pada rumus HLOOKUP, dipakai untuk memperoleh row_index
Sebagai contoh, kita gunakan tabel ibarat pola rumus VLOOKUP MATCH diatas, tetapi sudah di TRANSPOSE sehingga label kolom berpindah menjadi label baris.
Baris 1 berisi nama karyawan (range B1:G1)
Baris 2 berisi data pencapaian (range B2:G2)
Selanjutnya kita akan mencari data pencapaian karyawan tertentu memakai rumus.
Karena data disusun secara horizontal, maka fungsi yang sempurna untuk mengerjakan kiprah ini yaitu fungsi HLOOKUP.
Karena data disusun secara horizontal, maka fungsi yang sempurna untuk mengerjakan kiprah ini yaitu fungsi HLOOKUP.
Anggaplah nama karyawan yang akan dicek, disimpan di sel C5, dan label “Pencapaian” disimpan di sel A6
Maka rumus untuk mendapat data pencapaian karyawan yaitu :
=HLOOKUP(C5,B1:G2,MATCH("pencapaian",A1:A2,0),0)
Atau
=HLOOKUP(C5,B1:G2,MATCH(A6,A1:A2,0),0)
Kombinasi INDEX dan MATCH
Alternative lainnya, cara terkenal untuk melaksanakan Lookup yaitu memakai kombinasi fungsi INDEX dan MATCH.
Kombinasi fungsi ini sanggup menggantikan kedua pola kombinasi VLOOKUP/HLOOKUP dan MATCH yang sudah dibahas sebelumnya.
Kombinasi fungsi ini sanggup menggantikan kedua pola kombinasi VLOOKUP/HLOOKUP dan MATCH yang sudah dibahas sebelumnya.
Berbeda dengan fungsi MATCH yang dikombinasikan dengan VLOOKUP/HLOOKUP yang dipakai untuk mencari posisi (index) relative kolom/baris yang ditandai label kolom/baris (contoh label: pencarian)
Fungsi MATCH yang dikombinasikan dengan INDEX dipakai untuk mencari posisi relatif item data yang dicari itu sendiri.
Contohnya:
- Mencari posisi "unyil", "cuplis", "budi" dst…, (bukan label "nama")
- Mencari posisi 105%, 110%, 105, dst... (bukan label "pencapaian")
Mari kita rubah rumus kombinasi VLOOKUP/HLOOKUP – MATCH menjadi INDEX-MATCH
Contoh 1: INDEX MATCH Vertikal (Alternative VLOOKUP MATCH)
- range_nama = A2:A7
- range_pencapaian = B2:B7
- sel_nama = C10
- Formula untuk mendapat data persen "pencapaian" sasaran kerja karyawan?
- =index(range_pencapaian,match(sel_nama,range_nama,0:
- =INDEX(B2:B7,MATCH(C10,A2:A7,0))
Contoh 2: INDEX MATCH Horizontal (Alternative HLOOKUP MATCH)
- range_nama = B1:G1
- range_pencapaian = B2:G2
- sel_nama = C5
- Formula untuk mendapat data persen "pencapaian" sasaran kerja karyawan:
- =index(range_pencapaian,match(sel_nama,range_nama,0)
- =INDEX(B2:G2,MATCH(C5,B1:G1,0))
Untuk lebih memahami penggunaan contoh-contoh rumus yang disampaikan dalam postingan silahkan di download file ini: Excel 2007 - MATCH
Ringkasan:
Fungsi MATCH sanggup dipakai untuk mencari posisi relatif data yang dicari dalam sebuah tabel atau aray. Dari posisi relative tersebut kita sanggup mengexprolasi lebih jauh data terkait dengan mengkombinasikan MATCH dengan fungsi lainnya ibarat VLOOKUP, HLOOKUP dan INDEX.
Semoga Bermanfaat.
Belajar Excel! Excellent !
Artikel Terkait:
Referensi:
0 Response to "Fungsi Match - Kegunaan Dan Teladan Rumus"
Posting Komentar