Pada kesempatan ini juga akan diperlihatkan bagaimana cara menggunakan 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 dapat digunakan untuk melakukan 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 menggunakan fungsi Excel MATCH harus mengikuti aturan syntax berikut:
MATCH(lookup_value, lookup_array, [match_type])
Lookup_value (diperlukan) : Merupakan nilai yang ingin anda temukan, bisa berupa bilangan , text, maupun nilai logika. Nilai ini bisa diketikan langsung dalam rumus, maupun bisa ditempatkan pada sebuah sel referensi.
Lookup_array (diperlukan) : tabel atau array dimana dilakukan pencarian data
Match_type (opsional) = Menentukan metode pencarian, berupa kode 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 jika argumen match_type dikosongkan, maka fungsi MATCH akan menggunakan type 1 dalam melakukan pencarian data.
Untuk memudahkan pemahaman mengenai fungsi excel MATCH, mari kita membuat sebuah formula sederhana yang menggunakan data sebagai berikut:
- Kolom A (range A2:A7) berisi nama karyawan
- Kolom B (range B2:B7) berisi pencapaian target penjualan yang diraih oleh masing-masing karyawan.
Untuk mendapatkan posisi relatif salah seorang karyawan (misalnya si Budi) dalam list nama karyawan maka dapat menggunakan rumus berikut:
=MATCH("Budi",A2:A7,0)
Kita juga dapat menempatkan text Budi dalam sebuah referensi sel, anggaplah sel B13, sehingga rumusnya menjadi seperti di bawah ini:
=MATCH(B13,A2:A7,0)
Perhatikan penggunaan angka 0 sebagai argumen match_type.
Type 0 digunakan karena 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 adalah si “Budi” berada pada nomor urut ke-3
4 Hal Yang Perlu Diketahui Mengenai Fungsi Excel MATCH
Cara menggunakan 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 seperti yang dapat dilakukan oleh rumus VLOOKUP.
- Fungsi MATCH tidak bersifat case sensitive sehingga tidak membedakan penggunaan huruf kecil, ataupun huruf besar
- Jika ada lebih dari 1 nilai yang sama dalam lookup_array, maka fungsi MATCH hanya akan memberikan 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 bagian ini, diharapkan anda sudah mengenal basic dan cara penggunaan mendasar 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 dapat mengenali beberapa karakter wildcard berikut:
• Tanda Tanya (?) – mewakili karakter tunggal
• Tanda bintang atau asterisk (*) – mewakili sekumpulan karakter.
Catatan: Karakter wildcard hanya dapat digunakan dalam Rumus MATCH yang menggunakan match_type = 0
Formula MATCH dengan karakter wilcard berguna 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 contoh kasus berikut:
Anggaplah anda memiliki 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 bagian depan dari bagian namanya, misalnya anda ingat “bu”.
Misalnya nama karyawan disimpan di range B2:B7, maka rumus untuk mendapatkan posisi nama karyawan yang mengandung text “bu” adalah 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 bisa mengetahui posisisi budi dalam range.
Ya memang contoh tersebut hanya sebagai gambaran saja. Manfaat sebenarnya akan terasa jika kita bekerja dengan data yang banyak, ratusan sampai ribuan baris.
Untuk menjadikan rumus lebih fleksibel, maka alangkah baiknya bagian text yang dicari tadi ditempatkan pada sebuah referensi sel.
Kemudian rumus kita modifikasi dengan menggabungkan karakter wildcard dengan sel referensi menggunakan bantuan rumus penggabung kata, bisa menggunakan fungsi CONCATENATE, ataupun menggunakan operator ampersand (&)
Untuk mewakili hanya satu karakter, maka kita dapat menggunakan tanda tanya (?) sebagai karakter wilcard.
Misalnya kita lupa apakah nama orang yang dicari iwan atau ivan. Maka kita bisa membuat 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 huruf kecil dan huruf besar.
Namun, bukan berarti kita tidak bisa melakukan Lookup secara case sensitive.
Nah, untuk menjadikannya case sensitive maka kita dapat mengkombinasikan fungsi MATCH dengan fungsi EXACT.
Perhatikan struktur formula berikut:
MATCH(TRUE, EXACT(lookup_array, lookup_value), 0)
Formula tersebut mengerjakan tugasnya dengan logika sebagai berikut:
- Fungsi EXACT digunakan 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 memberikan informasi posisi relative nilai TRUE pertama dalam array yang dihasilkan oleh rumus EXACT.
Perlu diperhatikan kembali bahwa karena ini merupakan rumus array, maka anda harus menekan Ctrl + Shift + Enter supaya rumus tersebut bisa bekerja
Asumsikan bahwa lookup_value adalah dalam sel E1, dan kemudian lookup_array adalah range A2:A9, maka formula dapat dituliskan sebagai berikut:
=MATCH(TRUE, EXACT(A2:A9,E1),0)
Screenshoot di bawah ini memperlihatkan bagaimana formula MATCH EXACT dapat 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 menggunakan microsoft excel.
Ada beberapa metode yang dapat dilakukan untuk melakukan tugas ini. Salah satunya adalah menggunakan formula ISNA/MATCH.
Fungsi ISNA digunakan untuk mengecek sel apakah bernilai error #N/A dan mengembalikan nilai TRUE jika sel berisi nilai error #N/A.
Anggaplah kita memiliki 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 adalah contoh rumus yang dapat digunakan untuk tugas tersebut.
Membandingkan dua kolom dengan mengabaikan huruf besar/kecil (case insensitive)
=IF(ISNA(MATCH(B2,A:A,0)),"Tidak ada di List","")
Membandingkan dua kolom memperhatikan huruf besar/kecil (case sensitive)
=IF(ISNA(MATCH(TRUE,EXACT(A:A,B2),0)),"Tidak Ada di List","")
Kombinasi VLOOKUP dan MATCH
Pada bagian ini diasumsikan bahwa anda sudah memahami cara menggunakan fungsi excel VLOOKUP. Fungsi ini merupakan salah satu fungsi excel yang sangat diajurkan untuk dikuasai.
Salah satu kelemahan rumus VLOOKUP yang cukup mengganggu adalah dimana fungsi ini tidak lagi memberikan hasil yang relevan ketika ada kolom yang dihapus atau disisipkan pada tabel lookup.
Hal tersebut disebabkan fungsi VLOOKUP menggunakan col_index yang bersifat statis untuk menentukan urutan kolom tertentu.
Fungsi MATCH datang sebagai solusi atas permasalahan ini. Alih-alih menggunakan angka statis sebagai col_index dalam rumus VLOOKUP, rumus MATCH dapat 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 penjelasan diatas, mari kita belajar dari studi kasus berikut:
Perhatikan kembali contoh tabel pertama yang digunakan dalam tutorial ini yaitu tabel berisi list nama karyawan di kolom A, dan data pencapaian target di kolom B.
Anggaplah kita ingin mengetahui berapa persen pencapaian target salah satu karyawan.
Untuk memudahkan pengecekan satu persatu, nama karyawan tidak diketikan langsung pada formula, melainkan diketikan pada sebuah referensi sel, misalnya sel B10.
Maka kita dapat membuat formula seperti 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 dapat mengamati, ternyata rumus vlookup tidak lagi memberikan hasil yang relevan setelah dilakukan penyisipan kolom antara kolom A dan B.
Apa sebab?
Penyebab masalah ini adalah 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 setelah dilakukan insert column.
Solusinya:
Sediakan col_index yang bersifat dinamis.
Cara untuk mendapatkan angka angka col_index adalah dengan menggunakan fungsi MATCH.
Cara untuk mendapatkan angka angka col_index adalah dengan menggunakan fungsi MATCH.
Anggaplah nama karyawan yang akan di cek ditempatkan pada sel C10, kemudian label "Pencapaian" disimpan di sel A11.
Kita tahu bahwa untuk mendapatkan nilai pencapaian bisa menggunakan rumus VLOOKUP yaitu:
=VLOOKUP(C10,A2:B7,2,0)
Perhatikan : argumen col_index = 2, argumen ini dapat kita ganti dengan fungsi match
=MATCH("pencapaian",,A1:B1,0)
Atau menggunakan referensi sel:
=MATCH(A11,A1:B1,0)
Fungsi MATCH tersebut digunakan untuk memperoleh posisi relative kolom “Pencapaian” yang juga sebagai col_index dalam fungsi VLOOKUP.
Sehingga rumus VLOOKUP dapat 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 karena 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 bisa menggunakan fungsi MATCH untuk memperbaki kinerja rumus HLOOKUP.
Jika pada rumus VLOOKUP, fungsi MATCH ini digunakan untuk memperoleh col_index, maka pada rumus HLOOKUP, digunakan untuk memperoleh row_index
Sebagai contoh, kita gunakan tabel seperti contoh 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 menggunakan rumus.
Karena data disusun secara horizontal, maka fungsi yang tepat untuk mengerjakan tugas ini adalah fungsi HLOOKUP.
Karena data disusun secara horizontal, maka fungsi yang tepat untuk mengerjakan tugas ini adalah fungsi HLOOKUP.
Anggaplah nama karyawan yang akan dicek, disimpan di sel C5, dan label “Pencapaian” disimpan di sel A6
Maka rumus untuk mendapatkan data pencapaian karyawan adalah :
=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 populer untuk melakukan Lookup adalah menggunakan kombinasi fungsi INDEX dan MATCH.
Kombinasi fungsi ini dapat menggantikan kedua contoh kombinasi VLOOKUP/HLOOKUP dan MATCH yang sudah dibahas sebelumnya.
Kombinasi fungsi ini dapat menggantikan kedua contoh kombinasi VLOOKUP/HLOOKUP dan MATCH yang sudah dibahas sebelumnya.
Berbeda dengan fungsi MATCH yang dikombinasikan dengan VLOOKUP/HLOOKUP yang digunakan untuk mencari posisi (index) relative kolom/baris yang ditandai label kolom/baris (contoh label: pencarian)
Fungsi MATCH yang dikombinasikan dengan INDEX digunakan 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 mendapatkan data persen "pencapaian" target 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 mendapatkan data persen "pencapaian" target 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 dapat digunakan untuk mencari posisi relatif data yang dicari dalam sebuah tabel atau aray. Dari posisi relative tersebut kita bisa mengexprolasi lebih jauh data terkait dengan mengkombinasikan MATCH dengan fungsi lainnya seperti VLOOKUP, HLOOKUP dan INDEX.
Semoga Bermanfaat.
Belajar Excel! Excellent !
Artikel Terkait:
Referensi:
wagalasehh....mantab banget gan
ReplyDeleteSolder uap portable
makasi gan ilmunyaaa
ReplyDelete