Cara Melakukan INDEX MATCH dengan Beberapa Kriteria di Excel

Beberapa tahun lalu, saya mengerjakan analisis kampanye pemasaran di mana saya harus membandingkan kinerja penjualan di berbagai wilayah. Datanya tersebar di beberapa lembar Excel, dan saya harus menarik angka penjualan produk tertentu ke dalam satu laporan ringkasan. Awalnya, saya mencoba mencari dan menyalin data secara manual, tetapi ternyata tidak semudah yang saya kira. Jika ada satu baris yang salah, seluruh laporan bisa berantakan.

Saat itulah saya menemukan INDEX MATCH(). Butuh beberapa kali percobaan untuk mendapatkan rumus yang benar, namun setelah melihat betapa mudahnya rumus ini menemukan dan menarik angka yang saya butuhkan, saya langsung rutin menggunakannya. Hanya dengan dua fungsi, saya bisa menarik tepat data yang saya perlukan, tak peduli seberapa tersebar data itu di berbagai spreadsheet.

Dalam artikel ini, saya akan menjelaskan bagaimana Anda bisa melakukan hal yang sama menggunakan fungsi INDEX() dan MATCH(). Selalu ada hal baru untuk dipelajari di Excel. Jika Anda pemula, saya sangat merekomendasikan kursus Introduction to Excel kami. Jika Anda sudah berpengalaman, coba kursus Advanced Excel Functions kami.

Sekilas Tentang INDEX MATCH

INDEX MATCH adalah cara singkat untuk menyebut kombinasi dua fungsi Excel yang bekerja bersama untuk melakukan pencarian tingkat lanjut. Kita juga bisa menyebutnya sebagai INDEX(MATCH()), tetapi dalam artikel ini saya akan menggunakan INDEX MATCH. Sekarang, mari kita lihat masing-masing fungsinya:

Fungsi INDEX() mengambil nilai dari sebuah sel berdasarkan posisinya dalam suatu rentang tertentu. Berikut sintaksnya:

=INDEX(array, row_num, [column_num])

Berikut penjelasannya:

• array adalah rentang sel tempat Anda ingin mengambil nilai.

• row_num adalah nomor baris dalam array dari mana nilai akan dikembalikan.

• column_num (opsional) adalah nomor kolom dalam array dari mana nilai akan dikembalikan.

Fungsi MATCH() mengidentifikasi posisi relatif suatu nilai dalam sebuah rentang. Sintaksnya:

=MATCH(lookup_value, lookup_array, [match_type])

Berikut penjelasannya:

• lookup_value adalah nilai yang ingin Anda cari.

• lookup_array adalah rentang tempat fungsi mencari nilai tersebut.

match_type bersifat opsional. 1 (default) menemukan nilai yang kurang dari atau sama dengan lookup_value (array harus diurutkan menaik). 0 menemukan kecocokan persis (array tidak perlu diurutkan). -1 menemukan nilai terkecil yang lebih besar dari atau sama dengan lookup_value (array harus diurutkan menurun).

Cara menggabungkan INDEX() dengan MATCH()

Dengan menempatkan MATCH() di dalam INDEX(), kita bisa membuat pencarian dinamis. Mari pahami dengan contoh: Misalkan Anda ingin mencari posisi David Wilson di dataset. Alih-alih mengisi nomor baris secara hardcode di INDEX(), gunakan MATCH() untuk menentukannya:

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))

Pada rumus di atas, MATCH("David Wilson", A2:A6, 0) menghasilkan 4, yaitu posisi baris. Lalu INDEX(C2:C6, 4) mengambil nilai dari baris ke-4 pada rentang C2:C6, yaitu Seattle.

Gabungkan INDEX() dengan MATCH(). Gambar oleh Penulis.

Agar lebih dinamis, Anda dapat mengganti David Wilson yang di-hardcode dengan referensi sel. Dengan begitu, rumus akan menyesuaikan otomatis berdasarkan nilai di D4:

=INDEX(C2:C6,MATCH(D4,A2:A6,0))

Ganti nilai hardcode di INDEX MATCH. Gambar oleh Penulis.

INDEX MATCH vs. VLOOKUP()

Sekarang Anda tahu cara kerja INDEX() dan MATCH() secara terpisah dan bagaimana menggabungkannya membuat pencarian lebih dinamis, mari lihat mengapa INDEX MATCH lebih baik daripada VLOOKUP().

• Berbeda dengan VLOOKUP(), yang mengharuskan kolom pencarian berada di sebelah kiri, INDEX MATCH memungkinkan Anda mengambil data dari kolom mana pun, apa pun posisinya.

• INDEX MATCH hanya memproses rentang sel yang diperlukan dibandingkan VLOOKUP(), yang memindai seluruh tabel. 

• Rumus yang menggunakan VLOOKUP() bisa rusak jika kolom disisipkan atau dihapus, karena bergantung pada indeks kolom statis. Di sisi lain, INDEX MATCH mereferensikan rentang dinamis sehingga rumus Anda tetap aman meskipun struktur data berubah.

• Dengan INDEX MATCH, kita tidak perlu menghitung nomor kolom secara manual. Cukup tentukan kolom pencarian dan kolom hasil, dan selesai. 

INDEX MATCH dengan Beberapa Kriteria

Saya sering bekerja dengan dataset yang berisi entri duplikat, dan mencari nilai di dalamnya sangat sulit. Kini saya menggunakan INDEX MATCH karena dapat menangani skenario ini dengan sangat mudah, tidak seperti rumus pencarian standar lainnya. Izinkan saya menjelaskan langkah demi langkah cara saya menggunakannya.

Siapkan data untuk beberapa kriteria

Pertama, buat dataset Anda dan pastikan tersusun rapi dalam bentuk tabel dengan header yang jelas untuk setiap kolom. Setiap baris harus mewakili satu catatan unik, dan setiap kolom berisi atribut data tertentu.

Sebagai contoh, berikut dataset sampel:

Dataset untuk INDEX MATCH dengan beberapa kriteria. Gambar oleh Penulis.

Tulis rumus untuk beberapa kriteria

Setelah data tersusun rapi, saatnya menulis rumus. Rumus INDEX MATCH mengambil nilai dari kolom lain dengan mengidentifikasi baris yang memenuhi beberapa kondisi. Ini dilakukan dengan menggabungkan pengujian logis dalam fungsi MATCH() dan menempatkannya di dalam fungsi INDEX().

Berikut sintaks dasarnya: 

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Penjelasan: 

• Return_range adalah rentang dari mana nilai akan dikembalikan.

• Criteria1, Criteria2, … adalah kondisi yang harus dipenuhi.

• Range1, Range2, … adalah rentang yang sesuai dengan kriteria tersebut.

Sekarang setelah data siap, mari lihat lebih dekat dua metode untuk menjawab pertanyaan kita: cara menggunakan INDEX MATCH dengan beberapa kriteria.

Gunakan kolom pembantu untuk kriteria kompleks

Jika dataset Anda memiliki banyak kondisi, gunakan kolom pembantu untuk menyederhanakan proses. Kolom ini akan menggabungkan semua kondisi ke dalam satu kolom agar pencarian lebih mudah. Misalnya, saya menggunakan dataset yang sama untuk membuat kolom pembantu dengan menggabungkan kolom First Name dan Salary:

=A2&B2

Buat kolom pembantu. Gambar oleh Penulis.

Kolom pembantu ini menyederhanakan rumus INDEX MATCH saya. Alih-alih menulis rumus array yang rumit dengan banyak kondisi, saya mereferensikan kolom pembantu dalam rumus saya untuk pendekatan yang lebih sederhana:

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))

INDEX MATCH dengan kolom pembantu. Gambar oleh Penulis.

Gabungkan beberapa kriteria dengan rumus array

Jika Anda tidak menyukai kolom pembantu, Anda bisa menggunakan rumus array untuk mencapai hasil yang sama. Rumus ini memungkinkan Anda mengevaluasi beberapa kriteria langsung di dalam fungsi MATCH(). Misalnya, berikut cara saya mencari gaji Alice di departemen HR:

Langkah 1: Tulis fungsi MATCH() dengan kondisi logis:

 MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0)

Dalam rumus ini, 1 memastikan fungsi MATCH() mencari baris di mana semua kondisi bernilai benar. (F4=A2:A11) memeriksa apakah nilai di F4 cocok dengan nilai mana pun dalam rentang A2:A11. (F5=B2:B11) memeriksa apakah nilai di F5 cocok dengan nilai mana pun dalam rentang B2:B11. Operator * berfungsi sebagai logika AND, memastikan semua kondisi terpenuhi.

Langkah 2: Bungkus fungsi MATCH() ini di dalam fungsi INDEX():

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))

Langkah 3: Finalisasi rumus. Jika Anda menggunakan Excel versi lama, tekan Ctrl+Shift+Enter untuk menjadikannya rumus array. Di versi terbaru, tekan Enter.

INDEX MATCH array dengan beberapa kriteria. Gambar oleh Penulis.

Penggunaan Lanjutan INDEX MATCH dengan Beberapa Kriteria

Anda bisa melakukan lebih banyak hal dengan fungsi INDEX MATCH. Mari lihat caranya: 

Gunakan INDEX MATCH dengan named range dan rentang dinamis

Saya menggunakan named range di Excel untuk memberi nama yang bermakna seperti results atau totalSales alih-alih referensi standar seperti A1:A10. Dengan begitu, lebih mudah mengelola rumus di berbagai sheet. 

Untuk menamai suatu rentang sel, pilih selnya dan tekan Ctrl + F3 (Windows) atau Cmd + F3 (Mac) untuk membuka Name Manager. Lalu, klik New, masukkan nama, dan klik OK.

Beri nama rentang. Gambar oleh Penulis.

Perbedaan antara named range dan rentang dinamis adalah named range merujuk ke kelompok sel tetap, sedangkan rentang dinamis otomatis menyesuaikan saat data ditambah atau dihapus.

Untuk mengatur rentang dinamis, pilih selnya. Pada tab Formulas, klik Name Manager atau tekan Ctrl + F3 untuk membuka Name Manager Excel dan klik New. Kotak dialog New Name akan muncul. Sekarang, di kolom Name masukkan nama yang diinginkan. Lalu, di kolom Refers to masukkan rumus untuk rentang dinamis. 

Atur rentang dinamis. Gambar oleh Penulis. 

Sekarang mari lihat contoh: Saya mendefinisikan dua rentang dinamis dan satu rentang statis:

• total_amount: =$F$2:INDEX($F:$F, COUNTA($F:$F))

• items_list: =$A$2:INDEX($A:$A, COUNTA($A:$A))

• lookup_value: =$I$3

Sekarang, saya menggunakan rentang ini di dalam rumus INDEX MATCH:

=INDEX(total_amount,MATCH(lookup_value,items_list,0))

Dan Anda bisa melihat rumusnya menjadi jauh lebih mudah dipahami dengan nama yang jelas.

Gunakan rentang dinamis dan named range dengan INDEX MATCH. Gambar oleh Penulis.

INDEX MATCH bertingkat untuk pencarian kompleks

Selain pekerjaan dasar, Anda juga bisa menggunakan fungsi INDEX MATCH bertingkat untuk menangani pencarian yang kompleks. Misalnya, saya memiliki dataset yang menunjukkan penjualan berdasarkan kategori produk di berbagai wilayah. 

Dataset mentah. Gambar oleh Penulis.

Saya ingin mencari penjualan furniture di wilayah East. Namun untuk itu, saya harus mencocokkan kategori produk (baris) dan wilayah (kolom), yang tidak bisa dilakukan oleh INDEX MATCH dasar. Itulah sebabnya saya menggunakan rumus INDEX MATCH bertingkat berikut:

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))

Begini cara kerjanya: INDEX() mengambil nilai dari rentang B2:D4, tetapi fungsi ini membutuhkan nomor baris dan nomor kolom untuk menentukan lokasi yang tepat. Jadi, MATCH(D6, A2:A4, 0) pertama menentukan nomor baris. Jika D6 berisi Furniture, ia mencari di kolom A2:A4 dan menemukannya di baris kedua.

Berikutnya, MATCH(D7, B1:D1, 0) menentukan nomor kolom. Jika D7 berisi East, ia melihat melintasi B1:D1 dan menemukannya di kolom kedua.

Setelah INDEX() mengetahui baris dan kolomnya, fungsi ini menampilkan nilai hasil. Dalam kasus kita, penjualan untuk Furniture di wilayah East adalah 450.

Gunakan INDEX MATCH bertingkat. Gambar oleh Penulis.

Saya menggunakan rumus ini alih-alih mencari manual di baris dan kolom karena rumus ini menangani semuanya dengan presisi. 

Tantangan Umum dan Tips Pemecahan Masalah

Saat mulai menggunakan INDEX MATCH, saya mengalami beberapa tantangan, dan saya tidak ingin Anda mengalami frustrasi yang sama. Jadi, saya akan membahas tantangan yang paling umum dan menunjukkan cara mengatasinya.

Tangani error dalam rumus INDEX MATCH

Error seperti #N/A dan #VALUE! terlihat menjengkelkan pada awalnya, tetapi cukup mudah diperbaiki. Mari lihat cara menemukan penyebab masalah dan langkah sederhana untuk menyelesaikannya.

Error #N/A terjadi ketika fungsi MATCH() tidak menemukan nilai. Ini karena nilai pencarian tidak ada di array pencarian, atau data mengandung spasi tersembunyi. Misalnya, saya pernah mereferensikan kolom yang salah saat menarik Nama Karyawan:

=INDEX(B2:B6,MATCH(E3,C2:C6,0))

Error #N/A di INDEX MATCH. Gambar oleh Penulis.

Untuk memperbaiki masalah seperti ini, pastikan nilai pencarian ada di array dan gunakan fungsi TRIM() untuk membersihkan spasi:

=TRIM(INDEX(B2:B6,MATCH(E3,A2:A6,0)))

Error #N/A di INDEX MATCH diperbaiki. Gambar oleh Penulis.

#VALUE! muncul ketika rumus tidak diatur sebagai rumus array. Misalnya, jika saya menggunakan fungsi MATCH() dan menyertakan lebih dari satu rentang, Excel menganggapnya sebagai rumus array. Namun, jika tidak disetel dengan benar, Excel akan menampilkan error #VALUE!.

=INDEX(C2:C6,MATCH(D4&E4,A2:A6&B2:B6,0))

Error #VALUE di INDEX MATCH. Gambar oleh Penulis.

Untuk mengatasinya, tekan Ctrl + Shift + Enter setelah Anda memasukkan rumus. Dengan begitu, Excel akan membungkus rumus dengan kurung kurawal {}, menandakan bahwa ini sekarang adalah rumus array. Namun jangan ketik kurung kurawal secara manual karena akan merusak rumus.

Error #VALUE di INDEX MATCH diperbaiki. Gambar oleh Penulis.

Optimalkan kinerja pada dataset besar

Pada dataset yang lebih besar, rumus saya sesekali melambat, sehingga saya harus menunggu pembaruan perhitungan. Jika Anda juga mengalami hal serupa, coba tips berikut: 

• Batasi rentang pencarian: Batasi rentang hanya pada yang diperlukan. Misalnya, alih-alih A:A, gunakan A1:A100 untuk mengurangi waktu komputasi.

• Gunakan kolom pembantu: Hitung terlebih dahulu kriteria yang kompleks dengan kolom pembantu. Ini akan mengurangi beban komputasi rumus array.

• Aktifkan mode perhitungan manual: Alihkan Excel ke mode perhitungan manual untuk menghindari perhitungan ulang terus-menerus. Setelah melakukan perubahan, tekan F9 untuk memperbarui rumus secara manual.

• Hindari fungsi volatil: Minimalkan penggunaan fungsi volatil seperti NOW(), RAND(), dan TODAY() bersamaan dengan INDEX MATCH. Fungsi-fungsi ini memicu perhitungan ulang setiap kali workbook diperbarui.

Penutup 

INDEX MATCH menghemat waktu dan menyederhanakan analisis data yang kompleks. Jika Anda bekerja dengan dataset besar, teknik ini patut dicoba. Namun, cara terbaik untuk memantapkan pemahaman adalah dengan latihan. Jadi, saya sarankan kerjakan beberapa dataset dan bereksperimenlah dengan apa yang telah Anda pelajari. Begitulah cara saya mengasah keterampilan.

Untuk memperdalam pengetahuan, lihat kursus Advanced Excel Functions kami untuk menguasai lebih banyak alat yang kuat. Namun jika Anda ingin membangun keahlian komprehensif dalam analisis data di Excel, saya rekomendasikan kursus Data Analysis in Excel. Kursus ini mencakup semuanya dari persiapan data hingga visualisasi.