Cara Menggunakan Fungsi INDEX dan MATCH di Excel

2024 Pengarang: Abigail Brown | [email protected]. Terakhir diubah: 2023-12-17 06:51

Yang Perlu Diketahui

Fungsi INDEX dapat digunakan sendiri, tetapi menggabungkan fungsi MATCH di dalamnya akan membuat pencarian lanjutan.
Fungsi bersarang ini lebih fleksibel daripada VLOOKUP dan dapat memberikan hasil lebih cepat.

Artikel ini menjelaskan cara menggunakan fungsi INDEX dan MATCH secara bersamaan di semua versi Excel, termasuk Excel 2019 dan Microsoft 365.

Apa Fungsi INDEX dan MATCH?

INDEX dan MATCH adalah fungsi pencarian Excel. Meskipun keduanya merupakan dua fungsi terpisah yang dapat digunakan sendiri-sendiri, keduanya juga dapat digabungkan untuk membuat rumus tingkat lanjut.

Fungsi INDEX mengembalikan nilai atau referensi ke nilai dari dalam pilihan tertentu. Misalnya, ini dapat digunakan untuk menemukan nilai di baris kedua dari kumpulan data, atau di baris kelima dan kolom ketiga.

Sementara INDEX sangat baik digunakan sendiri, MATCH bersarang dalam rumus membuatnya sedikit lebih berguna. Fungsi MATCH mencari item tertentu dalam rentang sel dan kemudian mengembalikan posisi relatif item dalam rentang tersebut. Misalnya, ini dapat digunakan untuk menentukan bahwa nama tertentu adalah item ketiga dalam daftar nama.

INDEX dan MATCH Sintaks & Argumen

Inilah cara kedua fungsi perlu ditulis agar Excel dapat memahaminya:

=INDEX(array, row_num, [column_num])

array adalah rentang sel yang akan digunakan rumus. Ini bisa berupa satu atau beberapa baris dan kolom, seperti A1:D5. Wajib.
row_num adalah baris dalam array untuk mengembalikan nilai, seperti 2 atau 18. Ini diperlukan kecuali ada column_num.
column_num adalah kolom dalam larik untuk mengembalikan nilai, seperti 1 atau 9. Ini opsional.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value adalah nilai yang ingin Anda cocokkan di lookup_array. Ini bisa berupa angka, teks, atau nilai logika yang diketik secara manual atau dirujuk melalui referensi sel. Ini wajib.
lookup_array adalah rentang sel yang harus dilihat. Ini bisa berupa satu baris atau satu kolom, seperti A2:D2 atau G1:G45. Ini wajib.
match_type dapat berupa -1, 0, atau 1. Ini menentukan bagaimana lookup_value dicocokkan dengan nilai-nilai di lookup_array (lihat di bawah). 1 adalah nilai default jika argumen ini dihilangkan.

Jenis Pencocokan Mana yang Digunakan
Jenis Pencocokan	Apa Fungsinya	Aturan	Contoh
1	Menemukan nilai terbesar yang kurang dari atau sama dengan lookup_value.	Nilai lookup_array harus ditempatkan dalam urutan menaik (mis., -2, -1, 0, 1, 2; atau A-Z;, atau FALSE, TRUE.	lookup_value adalah 25 tetapi hilang dari lookup_array, jadi posisi angka terkecil berikutnya, seperti 22, dikembalikan sebagai gantinya.
0	Menemukan nilai pertama yang persis sama dengan lookup_value.	Nilai lookup_array bisa dalam urutan apapun.	lookup_value adalah 25, sehingga mengembalikan posisi 25.
-1	Menemukan nilai terkecil yang lebih besar atau sama dengan lookup_value.	Nilai lookup_array harus ditempatkan dalam urutan menurun (mis., 2, 1, 0, -1, -2).	lookup_value adalah 25 tetapi hilang dari lookup_array, jadi posisi angka terbesar berikutnya, seperti 34, dikembalikan sebagai gantinya.

Gunakan 1 atau -1 untuk saat-saat ketika Anda perlu menjalankan pencarian perkiraan di sepanjang skala, seperti ketika berurusan dengan angka dan ketika perkiraan baik-baik saja. Tetapi ingat bahwa jika Anda tidak menentukan match_type, 1 akan menjadi default, yang dapat mengubah hasil jika Anda benar-benar menginginkan kecocokan yang tepat.

Contoh Rumus INDEX dan MATCH

Sebelum kita melihat bagaimana menggabungkan INDEX dan MATCH menjadi satu formula, kita perlu memahami bagaimana fungsi-fungsi ini bekerja sendiri.

INDEX Contoh

=INDEX(A1:B2, 2, 2)

=INDEX(A1:B1, 1)

=INDEX(2:2, 1)=INDEX(B1:B2, 1)

Dalam contoh pertama ini, ada empat rumus INDEX yang bisa kita gunakan untuk mendapatkan nilai yang berbeda:

=INDEX(A1:B2, 2, 2) melihat melalui A1:B2 untuk menemukan nilai di kolom kedua dan baris kedua, yaitu Stacy.
=INDEX(A1:B1, 1) melihat melalui A1:B1 untuk menemukan nilai di kolom pertama, yaitu Jon.
=INDEX(2:2, 1) melihat semua yang ada di baris kedua untuk menemukan nilai di kolom pertama, yaitu Tim.
=INDEX(B1:B2, 1) melihat melalui B1:B2 untuk menemukan nilai di baris pertama, yaitu Amy.

Contoh PERTANDINGAN

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

Berikut adalah empat contoh mudah dari fungsi MATCH:

=MATCH("Stacy", A2:D2, 0) sedang mencari Stacy dalam rentang A2:D2 dan menghasilkan 3 sebagai hasilnya.
=MATCH(14, D1:D2) sedang mencari 14 dalam rentang D1:D2, tetapi karena tidak ditemukan dalam tabel, MATCH menemukan nilai terbesar berikutnya itu kurang dari atau sama dengan 14, yang dalam hal ini adalah 13, yang berada di posisi 1 dari lookup_array.
=MATCH(14, D1:D2, -1) identik dengan rumus di atasnya, tetapi karena array tidak dalam urutan menurun seperti yang diperlukan -1, kami mendapatkan kesalahan.
=MATCH(13, A1:D1, 0) mencari 13 di baris pertama lembar, yang mengembalikan 4 karena ini adalah item keempat dalam larik ini.

Contoh PERTANDINGAN INDEX

Berikut adalah dua contoh di mana kita dapat menggabungkan INDEX dan MATCH dalam satu rumus:

Temukan Referensi Sel di Tabel

=INDEX(B2:B5, MATCH(F1, A2:A5))

Contoh ini menyusun rumus MATCH di dalam rumus INDEX. Tujuannya adalah untuk mengidentifikasi warna item menggunakan nomor item.

Jika Anda melihat gambarnya, Anda dapat melihat di baris "Terpisah" bagaimana rumus akan ditulis sendiri, tetapi karena kami menyusunnya, inilah yang terjadi:

MATCH(F1, A2:A5) mencari nilai F1 (8795) dalam kumpulan data A2:A5. Jika kita menghitung mundur kolomnya, kita bisa melihatnya 2, jadi itulah yang baru saja ditemukan oleh fungsi MATCH.
Array INDEX adalah B2:B5 karena kami akhirnya mencari nilai di kolom itu.
Fungsi INDEX sekarang dapat ditulis ulang seperti ini karena 2 adalah yang ditemukan MATCH: INDEX(B2:B5, 2, [column_num]).
Karena column_num adalah opsional, kita dapat menghapusnya dengan ini: INDEX(B2:B5, 2).
Jadi sekarang, ini seperti rumus INDEX biasa dimana kita mencari nilai item kedua di B2:B5, yaitu merah.

Mencari Judul Baris dan Kolom

=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))

Dalam contoh MATCH dan INDEX ini, kita melakukan pencarian dua arah. Idenya adalah untuk melihat berapa banyak uang yang kami hasilkan dari barang-barang Hijau di bulan Mei. Ini benar-benar mirip dengan contoh di atas, tetapi formula MATCH tambahan bersarang di INDEX.

MATCH(G1, A2:A13, 0) adalah soal pertama yang diselesaikan dalam rumus ini. Itu mencari G1 (kata "Mei") di A2:A13 untuk mendapatkan nilai tertentu. Kami tidak melihatnya di sini, tapi ini 5.
MATCH(G2, B1:E1, 0) adalah rumus MATCH kedua, dan ini sangat mirip dengan yang pertama tetapi mencari G2 (kata "Hijau") pada judul kolom di B1:E1. Yang ini memutuskan menjadi 3.
Kita sekarang dapat menulis ulang rumus INDEX seperti ini untuk memvisualisasikan apa yang terjadi: =INDEX(B2:E13, 5, 3). Ini terlihat di seluruh tabel, B2:E13, untuk baris kelima dan kolom ketiga, yang menghasilkan $180.

Aturan PERTANDINGAN dan INDEKS

Ada beberapa hal yang perlu diperhatikan saat menulis rumus dengan fungsi berikut:

MATCH tidak peka huruf besar/kecil, jadi huruf besar dan huruf kecil diperlakukan sama saat mencocokkan nilai teks.
MATCH mengembalikan N/A karena beberapa alasan: jika match_type adalah 0 dan lookup_value tidak ditemukan jika match_type adalah -1 dan lookup_array tidak dalam urutan menurun, jika match_type adalah 1 dan lookup_array tidak dalam urutan menaik urutan, dan jika lookup_array bukan satu baris atau kolom.
Anda dapat menggunakan karakter wildcard dalam argumen lookup_value jika match_type adalah 0 dan lookup_value adalah string teks. Tanda tanya cocok dengan karakter tunggal apa pun dan tanda bintang cocok dengan urutan karakter apa pun (mis.g., =MATCH("Jo", 1:1, 0)). Untuk menggunakan MATCH untuk menemukan tanda tanya atau asterisk yang sebenarnya, ketik ~ terlebih dahulu.
INDEX mengembalikan REF! jika row_num dan column_num tidak menunjuk ke sel di dalam array.

Fungsi Excel Terkait

Fungsi MATCH mirip dengan LOOKUP, tetapi MATCH mengembalikan posisi item, bukan item itu sendiri.

VLOOKUP adalah fungsi pencarian lain yang dapat Anda gunakan di Excel, tetapi tidak seperti MATCH yang membutuhkan INDEX untuk pencarian tingkat lanjut, rumus VLOOKUP hanya membutuhkan satu fungsi itu.