Pengolahan Data dengan INDEX-MATCH di Spreadsheet

Biasanya, saat seseorang pertama kali diajarkan menggunakan Excel, kemungkinan besar VLOOKUP akan diperkenalkan sebagai metode utama untuk pengolahan data. VLOOKUP memang merupakan fungsi yang kuat yang dapat menangani banyak tugas terkait pengelolaan data seperti diilustrasikan dalam Data Wrangling with VLOOKUP in Spreadsheets. Dalam tutorial yang sama, kami juga membahas dasar-dasar kombinasi INDEX-MATCH, yang merupakan metode lain untuk melakukan pencarian tabel vertikal yang memiliki beberapa keunggulan dibandingkan VLOOKUP dengan konsekuensi rumus yang lebih rumit.

Keunggulan tersebut mencakup fitur seperti:

• Referensi Kolom Dinamis: Memungkinkan Anda menyisipkan dan menghapus kolom dari dataset dengan aman tanpa memengaruhi fungsi pencarian.
• Kecepatan Pemrosesan Lebih Tinggi pada Dataset Besar: Jika tabel Anda sangat besar, Anda mungkin akan melihat bahwa INDEX-MATCH jauh lebih cepat daripada VLOOKUP karena INDEX-MATCH hanya memperhatikan kolom yang ingin Anda ambil dan kolom pencariannya.
• Lokasi Nilai Pencarian: VLOOKUP tidak dapat melihat nilai yang berada di sebelah kiri kolom pertama yang Anda pilih dalam rentang tabel, tetapi INDEX-MATCH bisa melihat secara horizontal juga, sehingga tidak memiliki keterbatasan tersebut.
• Batas Ukuran Nilai Pencarian: VLOOKUP tidak dapat mencari nilai yang lebih panjang dari 255 karakter, tetapi INDEX-MATCH bisa.

Selain itu, INDEX-MATCH juga memungkinkan pencocokan berdasarkan nilai dari beberapa kolom, dan pencocokan yang peka huruf besar-kecil, yang sangat berguna saat Anda harus berurusan dengan ID tertentu yang mungkin berbeda berdasarkan huruf kecil dan huruf besar. Itulah sebabnya tutorial ini bertujuan menggali lebih dalam fitur lanjutan dari kombinasi INDEX-MATCH, serta menampilkan contoh bagaimana fitur-fitur ini berguna.

Dasar-Dasar INDEX-MATCH

Dalam bentuk yang paling sederhana, INDEX-MATCH dapat digunakan dengan cara yang sama seperti VLOOKUP, untuk melakukan pencarian tabel vertikal sederhana berdasarkan kunci umum. Struktur dasar rumusnya dapat dilihat sebagai berikut:

=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))

• column_range: Rentang kolom yang nilainya ingin Anda ambil.
• lookup_value: Nilai yang ingin Anda cari (kunci).
• lookup_column_range: Rentang kolom yang berisi kunci yang ingin Anda cocokkan.
• match_type: Antara 0 atau 1. Dengan 0 menunjukkan pencocokan tepat dan 1 menunjukkan pencocokan perkiraan. Ini analog dengan flag FALSE/TRUE pada VLOOKUP.

Selanjutnya, mari kita ilustrasikan cara kerja INDEX-MATCH menggunakan beberapa tabel contoh. Tabel pertama berisi beberapa nama, serta beberapa kunci unik. Tabel kedua berisi kunci yang sama (meski tidak harus dalam urutan yang sama) dan beberapa deskripsi. Jadi, tugasnya adalah menggabungkan deskripsi di tabel kedua dengan nama di tabel pertama menggunakan INDEX-MATCH. Tabelnya dapat dilihat di bawah:

Seperti yang Anda lihat, tabel pertama tidak memiliki deskripsi. Sekarang mari kita lanjutkan menggabungkan nama dengan deskripsinya menggunakan INDEX-MATCH:

Mudah, bukan? Seperti yang terlihat di video, langkah pertama adalah memilih column_range yang berisi data yang ingin kita ambil di dalam fungsi INDEX. Dalam hal ini, yaitu Description (M2:M8). Langkah kedua adalah memilih lookup_value di dalam fungsi MATCH, yaitu sel B2. Terakhir, tambahkan lookup_column_range (L2:L8) di dalam fungsi MATCH, dan pilih 0 sebagai match_type karena kita menginginkan kecocokan tepat dari lookup_value. Sisanya tinggal menekan ENTER dan menyeret rumus ke bawah untuk akhirnya menggabungkan nama dengan deskripsinya.

INDEX-MATCH juga dapat digunakan untuk menggabungkan tabel yang berada di lembar terpisah seperti pada VLOOKUP. Contoh videonya dapat dilihat di bawah:

Catatan: Anda mungkin memperhatikan bahwa rentang kolom yang saya pilih di video cenderung dikelilingi tanda $. Ini dilakukan untuk memilih rentang tetap yang tidak berubah saat Anda menyeret rumus secara vertikal atau horizontal. Saya akan terus menggunakan notasi tersebut di sepanjang tutorial ini.

Sejauh ini semuanya berperilaku sangat mirip dengan VLOOKUP sederhana tetapi dengan sedikit lebih banyak kerumitan. Namun, seperti disebutkan di awal tutorial ini, ada berbagai keunggulan jika Anda memilih jalur INDEX-MATCH. Salah satu yang paling berguna adalah referensi kolom dinamis. Artinya, memungkinkan Anda menambahkan kolom ke rentang tabel tempat Anda melakukan pencarian nilai tanpa memengaruhi pencariannya sendiri.

Jika kita menambahkan kolom baru dalam contoh sederhana kita dan kita menggunakan VLOOKUP, kita harus menulis ulang rumus di tabel kiri untuk merujuk ke deskripsi, yang akan menjadi kolom ke-3. Namun, jika Anda menggunakan INDEX-MATCH, yang memiliki referensi kolom dinamis, hal ini tidak diperlukan. Perbandingan ini dapat dilihat pada video di bawah:

Contoh di atas mengilustrasikan bagaimana kolom yang melakukan pencarian menggunakan INDEX-MATCH menyesuaikan ketika kolom baru yang berisi jenis pesawat luar angkasa ditambahkan, sementara kolom yang menggunakan VLOOKUP tidak. Keunggulan INDEX-MATCH atas VLOOKUP ini sangat menonjol pada tugas yang melibatkan pencarian di tabel Excel besar seperti laporan penjualan bisnis. Dengan kata lain, bayangkan Anda memiliki beberapa dasbor bisnis yang dibuat di Excel dan semuanya merujuk ke satu tabel dengan semua data. Jika Anda menggunakan VLOOKUP dan suatu hari perlu menyisipkan kolom di tengah untuk menambahkan fitur lain, Anda harus mengubah pemanggilan fungsi VLOOKUP di semua dasbor yang bergantung pada tabel tersebut. Namun, jika Anda membangun dasbor menggunakan INDEX-MATCH sebagai metode pencarian, Anda tidak akan menghadapi masalah seperti itu.

INDEX-MATCH Lanjutan: Pencocokan Peka Huruf Besar-Kecil

Referensi kolom dinamis sangat berguna, tetapi itu bukan satu-satunya keunggulan yang ditawarkan INDEX-MATCH. Misalnya, dimungkinkan untuk meningkatkan sintaks dasar INDEX-MATCH untuk melakukan pencarian yang peka huruf besar-kecil. Secara khusus, kita akan menambahkan fungsi EXACT di dalam fungsi MATCH untuk mencapai ini. Sintaks detailnya dapat dilihat di bawah:

{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}

• data_range: Rentang tabel yang relevan yang mencakup kolom pencarian dan kolom yang diinginkan untuk diambil.
• lookup_value: Nilai yang ingin Anda cari (kunci)
• lookup_column_range: Rentang kolom yang berisi kunci yang ingin Anda cocokkan
• match_type: Antara 0 atau 1. Dengan 0 menunjukkan kecocokan tepat dan 1 menunjukkan kecocokan perkiraan. Ini analog dengan flag FALSE/TRUE pada VLOOKUP
• desired_column_number: Sama seperti nomor indeks kolom pada VLOOKUP. Ini adalah indeks kolom yang berisi data yang ingin Anda peroleh.

Pencocokan peka huruf besar-kecil menggunakan kombinasi INDEX-MATCH yang dimodifikasi ini dapat sangat berguna saat mencocokkan kunci yang berisi huruf dan dibedakan berdasarkan besar-kecilnya huruf tersebut (misalnya Anda memiliki dua kunci berbeda, yang satu "00567UUp" dan yang lain "00567UUP"). Situasi seperti ini dapat ditemui, misalnya, di Salesforce CRM yang banyak digunakan saat mencoba mencocokkan Account ID atau Sales Opportunities ID. Namun untuk saat ini, kita tidak akan menggunakan dataset Salesforce, melainkan menggunakan versi modifikasi dari tabel contoh kita pada video di bawah untuk mengilustrasikan cara kerja rumus di atas secara langsung.

Catatan: kombinasi fungsi di atas diapit oleh kurung kurawal. Ini berarti ini adalah rumus array dan harus dimasukkan dengan menekan Cntr+Shift+Enter.

Seperti yang Anda lihat, sintaks rumus ini bisa panjang untuk ditulis, tetapi hasilnya sesuai harapan. Ia mencari nilai seperti INDEX-MATCH biasa, tetapi dengan mempertimbangkan besar-kecilnya karakter. Keajaibannya dilakukan oleh fungsi EXACT yang mengembalikan array TRUE dan FALSE. Di mana pun bernilai TRUE dalam array ini, dapat dikatakan ada nilai dengan huruf besar-kecil yang sama persis. Setelah itu, fungsi MATCH akan mengambil posisi kemunculan TRUE pertama.

Harap dicatat bahwa sangat penting untuk menekan Cntrl+Shift+Enter saat Anda menyelesaikan penulisan rumus, jika tidak Anda akan mendapatkan galat #N/A yang bisa sulit didiagnosis.

INDEX-MATCH Lanjutan: Pencocokan Berdasarkan Beberapa Kriteria

Pencarian peka huruf besar-kecil di Excel cukup keren, tetapi ada satu fitur yang bahkan lebih baik; pencarian yang mempertimbangkan beberapa kriteria. Mempertimbangkan banyak kriteria adalah sesuatu yang umum dilakukan sebagai bagian dari fungsi hitung atau jumlah bersyarat seperti COUNTIFS atau SUMIFS, tetapi lebih jarang terlihat sebagai bagian dari fungsi pencarian di Excel. Namun demikian, hal ini bisa menguntungkan dalam situasi tertentu. Misalnya, ini bisa menjadi caranya jika, katakanlah, Anda memiliki file Excel HR, dan Anda ingin mencari gaji karyawan tertentu berdasarkan nama depan DAN nama belakang. Jika Anda hanya mempertimbangkan salah satu dari dua kolom tersebut, ada kemungkinan Anda mendapatkan gaji Karyawan yang salah karena bisa ada banyak yang memiliki nama depan atau nama belakang yang sama. Namun, kemungkinan mendapatkan orang yang tepat akan jauh lebih besar jika Anda mengambil nilai berdasarkan nama depan dan nama belakang sekaligus.

Dengan demikian, sekarang mari kita lihat struktur INDEX-MATCH yang mempertimbangkan beberapa kriteria:

{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}

Pada dasarnya, rumus ini memiliki komponen yang sama persis dengan INDEX-MATCH biasa, tetapi sekarang dapat mempertimbangkan $n$ nilai pencarian dengan jumlah rentang pencarian masing-masing. Jika Anda perlu mengingat kembali apa saja komponennya, Anda dapat menggulir ke atas ke sintaks dasar INDEX-MATCH. Alasan mengapa kita memiliki "1" dalam fungsi MATCH adalah karena rumus ini membuat array 1 dan 0 yang merepresentasikan kecocokan masing-masing kriteria yang diinginkan. Kemudian menggunakan fungsi MATCH untuk mengambil lokasi 1 pertama yang ditemukan.

Seperti halnya rumus peka huruf besar-kecil, rumus ini juga diapit oleh kurung kurawal, yang berarti Anda harus menekan Cntr+Shift+Enter saat selesai menulisnya karena ini adalah rumus array.

Mari kita lihat contoh bagaimana rumus ini bekerja secara langsung dengan mencoba memilih pesawat luar angkasa yang termasuk dalam ras tertentu dan memiliki kecepatan warp maksimum tertentu pada video di bawah:

Voilá! Sekarang Anda memiliki alat untuk mencocokkan argumen berdasarkan beberapa kunci. Perlu diingat bahwa dalam contoh kecil ini, saya hanya menggunakan dua, yaitu kunci dan kecepatan warp maksimum, tetapi secara teori, Anda bisa terus menambahkan kriteria jika diinginkan. Kembali ke contoh HR kita, Anda bisa mencocokkan berdasarkan nama depan dan nama belakang serta jabatan untuk meminimalkan kemungkinan mendapatkan gaji karyawan yang salah.

Kesimpulan

Selamat! Sekarang Anda sudah familiar dengan INDEX-MATCH dan berbagai hal yang dapat Anda lakukan dengannya melampaui sekadar pencarian vertikal sederhana. Jika suatu saat Anda ditugaskan menangani laporan Excel yang kompleks, kombinasi fungsi ini kemungkinan akan menjadi sahabat terbaik Anda. Seperti banyak orang, saya diajarkan VLOOKUP di sekolah, tetapi ketika saya mulai serius menggunakan Excel untuk tugas seperti pencarian peka huruf besar-kecil pada Salesforce account ID, saya menyadari bahwa INDEX-MATCH adalah pilihan yang jauh lebih baik dalam jangka panjang, meskipun penulisannya lebih merepotkan dan rawan kesalahan. Saya mendorong Anda untuk mencobanya saat lain kali Anda perlu melakukan pencarian vertikal dalam pekerjaan Excel Anda. Siapa tahu, mungkin Anda akan menyukainya seperti saya.

Jika Anda ingin belajar lebih jauh, silakan lihat Kursus Data Analysis with Spreadsheets dari DataCamp.