Di Excel, mengambil data tertentu secara akurat sering kali menjadi kebutuhan. Meskipun fungsi INDEX dan MATCH masing-masing memiliki kelebihannya masing-masing, menggabungkan keduanya akan membuka perangkat canggih untuk pencarian data. Bersama-sama, keduanya memfasilitasi berbagai kemampuan pencarian, mulai dari pencarian dasar horizontal dan vertikal hingga fungsi yang lebih canggih seperti pencarian dua arah, peka huruf besar-kecil, dan multi-kriteria. Menawarkan kemampuan yang ditingkatkan dibandingkan dengan VLOOKUP, pemasangan INDEX dan MATCH memungkinkan opsi pencarian data yang lebih luas. Dalam tutorial ini, mari kita selidiki kedalaman kemungkinan yang bisa mereka capai bersama.

• fungsi INDEX
• Fungsi MATCH
• Gabungkan INDEKS dan MATCH

• Pencarian dua arah
• Pencarian kiri
• Pencarian peka huruf besar/kecil
• Pertandingan terdekat
• Cari dengan berbagai kriteria
• Cari di beberapa kolom
• Cari nilai pertama yang tidak kosong
• Cari nilai numerik pertama
• Cari asosiasi MAX atau MIN
• Tip: Sesuaikan pesan kesalahan #N/A Anda sendiri

Cara menggunakan INDEX dan MATCH di Excel

Sebelum kita menggunakan fungsi INDEX dan MATCH, pastikan kita mengetahui bagaimana INDEX dan MATCH dapat membantu kita dalam mencari nilai terlebih dahulu.

Cara menggunakan fungsi INDEX di Excel

Grafik INDEKS fungsi di Excel mengembalikan nilai di lokasi tertentu dalam rentang tertentu. Sintaks fungsi INDEX adalah sebagai berikut:

• susunan (wajib) mengacu pada rentang tempat Anda ingin mengembalikan nilai.
• baris_num (wajib, kecuali kolom_num hadir) mengacu pada nomor baris array.
• kolom_num (opsional, tetapi diperlukan jika baris_num dihilangkan) mengacu pada nomor kolom array.

Misalnya untuk mengetahui skor Jeff, yang 6siswa dalam daftar, Anda dapat menggunakan fungsi INDEX seperti ini:

=INDEX(C2:C11,6)

Catatan: Kisaran C2: C11 disitulah skor tertera, sedangkan nomornya 6 menemukan nilai ujian dari 6siswa ke.

Di sini mari kita lakukan tes kecil. Untuk rumusnya =INDEKS(A1:C1,2), nilai apa yang akan dikembalikannya? --- Ya, itu akan kembali Tanggal lahir, yang 2nilai nd di baris tertentu.

Sekarang kita harus tahu bahwa fungsi INDEX dapat bekerja sempurna dengan rentang horizontal atau vertikal. Tetapi bagaimana jika kita membutuhkannya untuk mengembalikan nilai dalam rentang yang lebih besar dengan beberapa baris dan kolom? Nah, dalam hal ini, kita harus menerapkan nomor baris dan nomor kolom. Misalnya untuk mengetahui skor Jeff dalam rentang tabel alih-alih satu kolom, kita dapat menemukan skornya dengan a baris nomor 6 dan kolom nomor 3 dalam sel melalui A2 ke C11 seperti ini:

=INDEX(A2:C11,6,3)

Namun, untuk database yang sangat besar dengan banyak baris dan kolom, tentu tidak nyaman bagi kita untuk menerapkan rumus dengan nomor baris dan nomor kolom yang tepat. Dan inilah saatnya kita harus menggabungkan penggunaan fungsi MATCH.

Luar biasa! Menggunakan Tab Efisien di Excel Seperti Chrome, Edge, Firefox dan Safari!
Hemat 50% waktu Anda, dan kurangi ribuan klik mouse untuk Anda setiap hari!

Cara menggunakan fungsi MATCH di Excel

Fungsi MATCH di Excel mengembalikan nilai numerik, lokasi item tertentu dalam rentang yang diberikan. Sintaks fungsi MATCH adalah sebagai berikut:

• nilai lookup (wajib) mengacu pada nilai yang akan dicocokkan di pencarian_array.
• pencarian_array (wajib) mengacu pada rentang sel tempat Anda ingin MATCH mencari.
• kecocokan_type (pilihan): 1, 0 or -1.
  • 1 (default), MATCH akan menemukan nilai terbesar yang kurang dari atau sama dengan nilai lookup. Nilai-nilai dalam pencarian_array harus ditempatkan dalam urutan menaik.
  • 0, MATCH akan menemukan nilai pertama yang persis sama dengan nilai lookup. Nilai-nilai dalam pencarian_array bisa dalam urutan apa pun. (Untuk kasus di mana jenis pencocokan diatur ke 0, Anda dapat menggunakan karakter wildcard.)
  • -1, MATCH akan menemukan nilai terkecil yang lebih besar dari atau sama dengan nilai lookup. Nilai-nilai dalam pencarian_array harus ditempatkan dalam urutan menurun.

Misalnya untuk mengetahui posisi Vera dalam daftar nama, Anda dapat menggunakan rumus MATCH seperti ini:

=MATCH("Vera",A2:A11,0)

√ Catatan: Hasil “4” menunjukkan bahwa nama “Vera” berada di posisi ke-4 dalam daftar.

Sekarang setelah kita mengetahui tentang penggunaan dasar fungsi INDEX dan MATCH di Excel, mari menyingsingkan lengan baju kita dan bersiap untuk menggabungkan kedua fungsi tersebut.

Cara menggabungkan INDEX dan MATCH di Excel

Silakan lihat contoh di bawah ini untuk mengetahui bagaimana kita dapat menggabungkan fungsi INDEX dan MATCH:

Mencari skor Evelyn, dengan pengetahuan bahwa nilai ujian ada di 3kolom ke-rd, kita bisa gunakan fungsi MATCH untuk menentukan posisi baris secara otomatis tanpa perlu menghitungnya secara manual. Selanjutnya, kita dapat menggunakan fungsi INDEX untuk mengambil nilai di perpotongan baris yang diidentifikasi dan kolom ke-3:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Karena rumusnya mungkin terlihat agak rumit, mari kita bahas setiap bagiannya.

Grafik INDEKS rumus berisi tiga argumen:

• baris_num: PERTANDINGAN("Evelyn",A2:A11,0) memberi INDEX posisi baris nilai "Evelyn" dalam jangkauan A2: A11, Yang 5.
• kolom_num: 3 menentukan 3kolom ketiga untuk INDEX untuk menemukan skor dalam array.
• susunan: A2: C11 menginstruksikan INDEX untuk mengembalikan nilai yang cocok di perpotongan baris dan kolom yang ditentukan, dalam rentang mulai dari A2 ke C11. Akhirnya kita mendapatkan hasilnya 90.

Dalam rumus di atas, kami menggunakan nilai hardcode, "Evelyn". Namun, dalam praktiknya, nilai hardcode tidak praktis karena memerlukan modifikasi setiap kali kita mencari data yang berbeda, seperti skor siswa lain. Dalam skenario seperti itu, kita bisa memanfaatkan referensi sel untuk membuat rumus dinamis. Misalnya, dalam hal ini, saya akan melakukannya ubah "Evelyn" menjadi F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Contoh rumus INDEX dan MATCH

Pada bagian ini, kita akan berbicara tentang keadaan yang berbeda untuk menggunakan fungsi INDEX dan MATCH untuk memenuhi kebutuhan yang berbeda.

INDEX dan MATCH untuk menerapkan pencarian dua arah

Pada contoh sebelumnya, kita mengetahui nomor kolom dan menggunakan rumus MATCH untuk mencari nomor baris. Namun bagaimana jika kita juga ragu dengan nomor kolomnya?

Dalam kasus seperti ini, kita dapat melakukan pencarian dua arah, juga dikenal sebagai pencarian matriks, dengan menggunakan dua fungsi MATCH: satu untuk mencari nomor baris dan yang lainnya untuk menentukan nomor kolom. Misalnya untuk mengetahui skor Evelyn, kita harus menggunakan rumus:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX dan MATCH untuk menerapkan pencarian kiri

Sekarang, mari kita pertimbangkan skenario di mana Anda perlu menentukan kelas Evelyn. Anda mungkin telah memperhatikan bahwa kolom kelas diposisikan di sebelah kiri kolom nama, situasi yang melebihi kemampuan fungsi pencarian Excel canggih lainnya, VLOOKUP.

Faktanya, kemampuan untuk melakukan pencarian sisi kiri adalah salah satu aspek yang membuat kombinasi INDEX dan MATCH mengungguli VLOOKUP.

Mencari Kelas Evelyn, gunakan rumus berikut untuk cari Evelyn di B2:B11 dan mengambil nilai yang sesuai dari A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX dan MATCH untuk menerapkan pencarian peka huruf besar-kecil

Fungsi MATCH pada dasarnya tidak membedakan huruf besar dan kecil. Namun, ketika Anda memerlukan rumus untuk membedakan karakter huruf besar dan kecil, Anda dapat menyempurnakannya dengan memasukkan TEPAT fungsi. Dengan menggabungkan fungsi MATCH dengan EXACT dalam rumus INDEX, Anda kemudian dapat melakukan pencarian peka huruf besar-kecil secara efektif, seperti yang ditunjukkan di bawah ini:

• susunan mengacu pada rentang tempat Anda ingin mengembalikan nilainya.
• nilai lookup mengacu pada nilai yang akan dicocokkan, dengan mempertimbangkan kasus karakter, dalam pencarian_array.
• pencarian_array mengacu pada rentang sel yang ingin Anda bandingkan dengan MATCH nilai lookup.

Misalnya untuk mengetahui Nilai ujian JIMMY, gunakan rumus berikut:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

Catatan: Ini adalah rumus array yang mengharuskan Anda memasukkan dengan Ctrl + perubahan + Enter, kecuali di Excel 365 dan Excel 2021.

INDEX dan MATCH untuk menemukan kecocokan terdekat

Di Excel, Anda mungkin menghadapi situasi di mana Anda perlu menemukan kecocokan terdekat atau terdekat dengan nilai tertentu dalam kumpulan data. Dalam skenario seperti itu, penggunaan kombinasi fungsi INDEX dan MATCH, serta fungsi ABS dan MIN, bisa sangat membantu.

• susunan mengacu pada rentang tempat Anda ingin mengembalikan nilainya.
• pencarian_array mengacu pada rentang nilai yang ingin Anda temukan kecocokannya paling dekat nilai lookup.
• nilai lookup mengacu pada nilai untuk menemukan kecocokan terdekatnya.

Misalnya untuk mencari tahu yang skornya paling mendekati 85, gunakan rumus berikut untuk cari skor terdekat dengan 85 di C2:C11 dan mengambil nilai yang sesuai dari A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

Catatan: Ini adalah rumus array yang mengharuskan Anda memasukkan dengan Ctrl + perubahan + Enter, kecuali di Excel 365 dan Excel 2021.

Luar biasa! Menggunakan Tab Efisien di Excel Seperti Chrome, Edge, Firefox dan Safari!
Hemat 50% waktu Anda, dan kurangi ribuan klik mouse untuk Anda setiap hari!

INDEX dan MATCH untuk menerapkan pencarian dengan beberapa kriteria

Untuk menemukan nilai yang memenuhi beberapa kondisi, yang mengharuskan Anda mencari di dua kolom atau lebih, gunakan rumus berikut. Rumusnya memungkinkan Anda melakukan pencarian multi-kriteria dengan menentukan berbagai kondisi di kolom berbeda, membantu Anda menemukan nilai yang diinginkan yang memenuhi semua kriteria yang ditentukan.

Catatan: Ini adalah rumus array yang mengharuskan Anda memasukkan dengan Ctrl + perubahan + Enter. Sepasang kurung kurawal kemudian akan muncul di bilah rumus.

• susunan mengacu pada rentang tempat Anda ingin mengembalikan nilainya.
• (nilai_pencarian=pencarian_array) mewakili satu kondisi. Kondisi ini memeriksa apakah tertentu nilai lookup cocok dengan nilai-nilai dalam pencarian_array.

Misalnya untuk mencari skor Coco Kelas A, yang tanggal lahirnya 7/2/2008, Anda dapat menggunakan rumus berikut:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX dan MATCH untuk menerapkan pencarian di beberapa kolom

Bayangkan sebuah skenario di mana Anda berurusan dengan beberapa kolom data. Kolom pertama berfungsi sebagai kunci untuk mengklasifikasikan data pada kolom lainnya. Untuk menentukan kategori atau klasifikasi entri tertentu, Anda harus melakukan pencarian di seluruh kolom data dan mengaitkannya dengan kunci yang relevan di kolom referensi.

Misalnya, pada tabel di bawah, bagaimana kita bisa mencocokkan siswa Shawn dengan kelasnya yang sesuai menggunakan INDEX dan MATCH? Ya, Anda bisa mencapainya dengan rumus, namun rumusnya cukup luas dan sulit untuk dipahami, apalagi diingat dan diketik.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Di situlah Kutools untuk Excel's Indeks dan Pencocokan pada Beberapa Kolom fitur ini berguna. Ini menyederhanakan proses, membuatnya cepat dan mudah untuk mencocokkan entri tertentu dengan kategori yang sesuai. Untuk membuka kunci alat canggih ini dan dengan mudah mencocokkan Shawn dengan kelasnya, cukup saja unduh dan instal add-in Kutools for Excel, lalu lakukan hal berikut:

1. Pilih sel tujuan tempat Anda ingin menampilkan kelas yang cocok.
2. pada Kutools tab, klik Pembantu Formula > Pencarian & Referensi > Indeks dan Pencocokan pada Beberapa Kolom.
3. 
3. Di kotak dialog pop-up, lakukan hal berikut:
   1. Klik tanggal 1 tombol di sebelahnya cari_kol untuk memilih kolom yang berisi informasi penting yang ingin Anda kembalikan, yaitu nama kelas. (Anda hanya dapat memilih satu kolom di sini.)
   2. Klik yang ke-2 tombol di sebelahnya Tabel_rng untuk memilih sel agar sesuai dengan nilai yang dipilih cari_kol, yaitu nama siswa.
   3. Klik yang ke-3 tombol di sebelahnya Nilai lookup untuk memilih sel berisi nama siswa yang ingin dicocokkan dengan kelasnya, dalam hal ini Shawn.
   4. Klik OK.
   5. 

Hasil

Kutools secara otomatis membuat rumusnya, dan Anda akan segera melihat nama kelas Shawn ditampilkan di sel tujuan.

INDEX dan MATCH untuk mencari nilai pertama yang tidak kosong

Untuk mengambil nilai pertama yang tidak kosong, dengan mengabaikan kesalahan, dari kolom atau baris, Anda bisa menggunakan rumus berdasarkan fungsi INDEX dan MATCH. Namun, jika Anda tidak ingin mengabaikan kesalahan dari jangkauan Anda, tambahkan fungsi ISBLANK.

• Dapatkan nilai tidak kosong pertama di kolom atau baris dengan mengabaikan kesalahan:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Dapatkan nilai tidak kosong pertama di kolom atau baris termasuk kesalahan:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX dan MATCH untuk mencari nilai numerik pertama

Untuk mengambil nilai numerik pertama dari kolom atau baris, gunakan rumus berdasarkan fungsi INDEX, MATCH, dan ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX dan MATCH untuk mencari asosiasi MAX atau MIN

Jika Anda perlu mengambil nilai yang terkait dengan nilai maksimum atau minimum dalam suatu rentang, Anda dapat menggunakan fungsi MAX atau MIN bersama dengan fungsi INDEX dan MATCH.

• INDEX dan MATCH untuk mengambil nilai yang terkait dengan nilai maksimum:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX dan MATCH untuk mengambil nilai yang terkait dengan nilai minimum:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Ada dua argumen dalam rumus di atas:
• • susunan mengacu pada rentang tempat Anda ingin mengembalikan informasi terkait.
  • pencarian_array mewakili kumpulan nilai yang akan diperiksa atau dicari kriteria tertentu, yaitu nilai maksimum atau minimum.

Misalnya saja jika Anda ingin menentukan siapa yang mempunyai skor tertinggi, gunakan rumus berikut:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Tip: Sesuaikan pesan kesalahan #N/A Anda sendiri

Saat bekerja dengan fungsi INDEX dan MATCH Excel, Anda mungkin mengalami kesalahan #N/A ketika tidak ada hasil yang cocok. Misalnya, pada tabel di bawah, ketika mencoba mencari skor seorang siswa bernama Samantha, kesalahan #N/A muncul karena dia tidak ada dalam kumpulan data.

Untuk membuat spreadsheet Anda lebih mudah digunakan, Anda dapat menyesuaikan pesan kesalahan ini dengan menggabungkan rumus INDEX MATCH Anda dalam fungsi IFNA:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Di atas adalah semua konten relevan terkait fungsi INDEX dan MATCH di Excel. Saya harap tutorial ini bermanfaat bagi Anda. Jika Anda ingin menjelajahi lebih banyak tips dan trik Excel, silakan klik di sini untuk mengakses koleksi ekstensif kami yang berisi lebih dari ribuan tutorial.