Mengapa INDEX dan MATCH lebih baik daripada VLOOKUP di Excel

Kami sebelumnya telah menjelaskan kepada pemula cara menggunakan fungsi dasar VLOOKUP (VLOOKUP bahasa Inggris, singkatan dari "fungsi pencarian vertikal"). Dan pengguna berpengalaman diperlihatkan beberapa formula yang lebih rumit.

Dan pada artikel ini kami akan mencoba memberikan informasi tentang metode lain dalam bekerja dengan pencarian vertikal.

Anda mungkin bertanya-tanya: "Mengapa ini perlu?". Dan ini diperlukan untuk menunjukkan semua metode pencarian yang mungkin. Selain itu, banyak pembatasan VLOOKUP sering kali mencegah mendapatkan hasil yang diinginkan. Dalam hal ini, INDEX( ) MATCH() jauh lebih fungsional dan beragam, dan juga memiliki batasan yang lebih sedikit.

Dasar-dasar INDEX MATCH

Karena tujuan dari panduan ini adalah untuk menunjukkan seberapa bagus fitur ini, kami Mari kita lihat informasi dasar mengenai prinsip-prinsip operasinya. Dan kami akan menunjukkan contoh, dan juga mempertimbangkan mengapa, ini lebih baik daripada VLOOKUP ().

Sintaks dan Penggunaan Fungsi INDEX

Fungsi ini membantu dalam menemukan nilai yang diinginkan di antara area pencarian yang ditentukan berdasarkan kolom atau nomor baris. sintaks:

=INDEX(array, nomor baris, nomor kolom):

• array – area di mana pencarian akan dilakukan;
• nomor baris – jumlah baris yang akan dicari dalam array yang ditentukan. Jika nomor baris tidak diketahui, nomor kolom harus ditentukan;
• nomor kolom – jumlah kolom yang akan ditemukan dalam larik yang ditentukan. Jika nilainya tidak diketahui, nomor baris diperlukan.

Contoh rumus sederhana:

=INDEKS(A1:S10,2,3)

Fungsi akan mencari dalam rentang dari A1 hingga C10. Angka-angka menunjukkan baris (2) dan kolom (3) mana yang menunjukkan nilai yang diinginkan. Hasilnya adalah sel C2.

Cukup sederhana, bukan? Tetapi ketika Anda bekerja dengan dokumen nyata, Anda tidak mungkin memiliki informasi mengenai nomor kolom atau sel. Untuk itulah fungsi MATCH().

MATCH Fungsi Sintaks dan Penggunaan

Fungsi MATCH() mencari nilai yang diinginkan dan menunjukkan angka perkiraannya di area pencarian yang ditentukan.

Sintaks searchpos() terlihat seperti ini:

=MATCH(nilai yang akan dicari, array yang akan dicari, jenis yang cocok)

• nilai pencarian – nomor atau teks yang akan ditemukan;
• array yang dicari – area di mana pencarian akan dilakukan;
• jenis pencocokan – menentukan apakah akan mencari nilai yang tepat atau nilai yang paling dekat dengannya:
  • 1 (atau tidak ada nilai yang ditentukan) – mengembalikan nilai terbesar yang sama dengan atau kurang dari nilai yang ditentukan;
  • 0 – menunjukkan kecocokan persis dengan nilai yang dicari. Dalam kombinasi INDEX() MATCH() Anda hampir selalu membutuhkan kecocokan yang tepat, jadi kami menulis 0;
  • -1 – Menampilkan nilai terkecil yang lebih besar atau sama dengan nilai yang ditentukan dalam rumus. Pengurutan dilakukan dalam urutan menurun.

Misalnya, dalam rentang B1:B3 New York, Paris, London terdaftar. Rumus di bawah ini akan menunjukkan angka 3 karena London berada di urutan ketiga dalam daftar:

=EKSPOS(London,B1:B3,0)

Cara bekerja dengan fungsi INDEX MATCH 

Anda mungkin sudah mulai memahami prinsip di mana kerja bersama dari fungsi-fungsi ini dibangun. Singkatnya, lalu INDEX() mencari nilai yang diinginkan di antara baris dan kolom yang ditentukan. Dan MATCH() menunjukkan jumlah nilai-nilai ini:

=INDEX(kolom dari mana nilai dikembalikan, MATCH(nilai untuk dicari, kolom untuk dicari, 0))

Masih kesulitan memahami cara kerjanya? Mungkin contoh akan menjelaskan lebih baik. Misalkan Anda memiliki daftar ibu kota dunia dan populasinya:

Untuk mengetahui besarnya jumlah penduduk suatu ibu kota tertentu, misalnya ibu kota negara Jepang, kita menggunakan rumus sebagai berikut:

=INDEX(C2:C10, MATCH(Jepang, A2:A10,0))

Penjelasan:

• Fungsi MATCH() mencari nilai – “Jepang” dalam larik A2:A10 dan mengembalikan angka 3, karena Jepang adalah nilai ketiga dalam daftar. 
• Angka ini menuju kenomor baris” dalam rumus INDEX() dan memberi tahu fungsi untuk mencetak nilai dari baris ini.

Jadi rumus di atas menjadi rumus standar INDEKS(C2:C10,3). Rumus mencari dari sel C2 ke C10 dan mengembalikan data dari sel ketiga dalam rentang ini, yaitu C4, karena hitungan mundur dimulai dari baris kedua.

Tidak ingin meresepkan nama kota dalam rumus? Kemudian tulis di sel mana saja, katakan F1, dan gunakan sebagai referensi dalam rumus MATCH(). Dan Anda berakhir dengan rumus pencarian dinamis:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Penting! Jumlah baris dalam susunan INDEX() harus sama dengan jumlah baris dalam dianggap array di MATCH(), jika tidak, Anda akan mendapatkan hasil yang salah.

Tunggu dulu, kenapa tidak menggunakan rumus VLOOKUP() saja?

=VLOOKUP(F1, A2:C10, 3, Salah)

 Apa gunanya membuang-buang waktu untuk mencari tahu semua kerumitan INDEX MATCH ini?

Dalam hal ini, tidak masalah fungsi mana yang digunakan. Ini hanyalah sebuah contoh untuk memahami bagaimana fungsi INDEX() dan MATCH() bekerja bersama. Contoh lain akan menunjukkan kemampuan fungsi ini dalam situasi di mana VLOOKUP tidak berdaya. 

PERTANDINGAN INDEKS atau VLOOKUP

Saat memutuskan rumus pencarian mana yang akan digunakan, banyak yang setuju bahwa INDEX() dan MATCH() jauh lebih unggul daripada VLOOKUP. Namun, banyak orang masih menggunakan VLOOKUP(). Pertama, VLOOKUP() lebih sederhana, dan kedua, pengguna tidak sepenuhnya memahami semua keuntungan bekerja dengan INDEX() dan MATCH(). Tanpa pengetahuan ini, tidak ada yang akan setuju untuk menghabiskan waktu mereka mempelajari sistem yang kompleks.

Berikut adalah keuntungan utama INDEX() dan MATCH() dibandingkan VLOOKUP():

• Cari dari kanan ke kiri. VLOOKUP() tidak dapat mencari dari kanan ke kiri, sehingga nilai yang Anda cari harus selalu berada di kolom paling kiri tabel. Tetapi INDEX() dan MATCH() dapat menangani ini tanpa masalah. Artikel ini akan memberi tahu Anda seperti apa praktiknya: cara menemukan nilai yang diinginkan di sisi kiri.

1. Penambahan atau penghapusan kolom yang aman. Rumus VLOOKUP() menunjukkan hasil yang salah saat menghapus atau menambahkan kolom karena VLOOKUP() membutuhkan nomor kolom yang tepat agar berhasil. Secara alami, ketika kolom ditambahkan atau dihapus, jumlahnya juga berubah. 

Dan dalam rumus INDEX() dan MATCH(), rentang kolom ditentukan, bukan kolom individual. Hasilnya, Anda dapat dengan aman menambahkan dan menghapus kolom tanpa harus memperbarui rumus setiap kali.

1. Tidak ada batasan pada volume pencarian. Saat menggunakan VLOOKUP(), jumlah total kriteria pencarian tidak boleh melebihi 255 karakter atau Anda akan mendapatkan #VALUE! Jadi jika data Anda berisi banyak karakter, INDEX() dan MATCH() adalah pilihan terbaik.
2. Kecepatan pemrosesan tinggi. Jika tabel Anda relatif kecil, maka Anda tidak akan melihat perbedaan apa pun. Tetapi, jika tabel berisi ratusan atau ribuan baris, dan, karenanya, ada ratusan dan ribuan rumus, INDEX () dan MATCH () akan mengatasi lebih cepat daripada VLOOKUP (). Faktanya adalah bahwa Excel hanya akan memproses kolom yang ditentukan dalam rumus, alih-alih memproses seluruh tabel. 

Dampak kinerja VLOOKUP() akan sangat terlihat jika lembar kerja Anda berisi sejumlah besar rumus seperti VLOOKUP() dan SUM(). Pemeriksaan terpisah dari fungsi VLOOKUP() diperlukan untuk mengurai setiap nilai dalam array. Jadi Excel harus memproses sejumlah besar informasi, dan ini memperlambat pekerjaan secara signifikan.

Contoh Formula 

Kami telah mengetahui kegunaan fungsi-fungsi ini, sehingga kami dapat beralih ke bagian yang paling menarik: penerapan pengetahuan dalam praktik.

Rumus untuk mencari dari kanan ke kiri

Seperti yang telah disebutkan, VLOOKUP tidak dapat melakukan bentuk pencarian ini. Jadi, jika nilai yang diinginkan tidak ada di kolom paling kiri, VLOOKUP() tidak akan menghasilkan hasil. Fungsi INDEX() dan MATCH() lebih fleksibel, dan lokasi nilai tidak memainkan peran besar untuk berfungsi.

Misalnya, kita akan menambahkan kolom peringkat di sisi kiri tabel kita dan mencoba mencari tahu peringkat apa dalam hal populasi yang ditempati ibu kota Negara Kita.

Di sel G1, kami menulis nilai yang akan ditemukan, dan kemudian menggunakan rumus berikut untuk mencari dalam rentang C1:C10 dan mengembalikan nilai yang sesuai dari A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Mengingatkan. Jika Anda berencana menggunakan rumus ini untuk beberapa sel, pastikan Anda memperbaiki rentang menggunakan pengalamatan absolut (misalnya, $A$2: $A$10 dan $C$2: 4C$10).

INDEX LEBIH TERKENALAN LEBIH TERJADI  untuk mencari di kolom dan baris

Dalam contoh di atas, kami telah menggunakan fungsi ini sebagai pengganti VLOOKUP() untuk mengembalikan nilai dari rentang baris yang telah ditentukan. Tetapi bagaimana jika Anda perlu melakukan pencarian matriks atau dua sisi?

Kedengarannya rumit, tetapi rumus untuk perhitungan tersebut mirip dengan rumus standar INDEX() MATCH(), dengan hanya satu perbedaan: rumus MATCH() harus digunakan dua kali. Pertama kali mendapatkan nomor baris, dan kedua kalinya mendapatkan nomor kolom:

=INDEX(array, MATCH(nilai pencarian vertikal, kolom pencarian, 0), MATCH(nilai pencarian horizontal, baris pencarian, 0))

Mari kita lihat tabel di bawah ini dan coba buat rumusnya INDEKS() EKSPRES() EKSPRES() untuk menampilkan demografi di negara tertentu untuk tahun yang dipilih.

Negara target ada di sel G1 (pencarian vertikal) dan tahun target ada di sel G2 (pencarian horizontal). Rumusnya akan terlihat seperti ini:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Bagaimana rumus ini bekerja

Seperti halnya rumus kompleks lainnya, rumus tersebut lebih mudah dipahami dengan memecahnya menjadi persamaan individu. Dan kemudian Anda dapat memahami apa yang dilakukan setiap fungsi individu:

• PERTANDINGAN(G1,A2:A11,0) – mencari nilai (G1) dalam rentang A2:A11 dan menunjukkan jumlah nilai ini, dalam kasus kami adalah 2;
• CARI(G2,B1:D1,0) – mencari nilai (G2) dalam rentang B1:D1. Dalam hal ini, hasilnya adalah 3.

Nomor baris dan kolom yang ditemukan dikirim ke nilai yang sesuai dalam rumus INDEX():

=INDEKS(B2:D11,2,3)

Akibatnya, kami memiliki nilai yang ada di sel di persimpangan 2 baris dan 3 kolom dalam rentang B2:D11. Dan rumus menunjukkan nilai yang diinginkan, yang ada di sel D3.

Cari berdasarkan beberapa kondisi dengan INDEX dan MATCH

Jika Anda telah membaca panduan kami untuk VLOOKUP(), Anda mungkin telah mencoba beberapa rumus pencarian. Tetapi metode pencarian ini memiliki satu batasan signifikan – kebutuhan untuk menambahkan kolom tambahan.

Tapi kabar baiknya adalah Dengan INDEX() dan MATCH() Anda dapat mencari beberapa kondisi tanpa harus mengedit atau mengubah lembar kerja Anda.

Berikut adalah rumus pencarian multi-kondisi umum untuk INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Catatan: rumus ini harus digunakan bersama dengan pintasan keyboard CTRL+SHIFT+ENTER.

Misalkan Anda perlu menemukan nilai yang Anda cari berdasarkan 2 kondisi: Pelanggan и Produk.

Ini membutuhkan rumus berikut:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

Dalam rumus ini, C2:C10 adalah rentang di mana pencarian akan dilakukan, F1 - keadaan ini, A2:A10 — adalah rentang untuk membandingkan kondisi, F2 - syarat 2, V2: V10 – rentang untuk perbandingan kondisi 2.

Jangan lupa tekan kombinasi di akhir pekerjaan dengan rumus CTRL+SHIFT+ENTER – Excel akan secara otomatis menutup rumus dengan kurung kurawal, seperti yang ditunjukkan pada contoh:

Jika Anda tidak ingin menggunakan rumus array untuk pekerjaan Anda, tambahkan INDEX() lain ke rumus dan tekan ENTER, maka akan terlihat seperti pada contoh:

Bagaimana rumus ini bekerja

Rumus ini bekerja dengan cara yang sama seperti rumus INDEX() MATCH() standar. Untuk mencari beberapa kondisi, Anda cukup membuat beberapa kondisi Salah dan Benar yang mewakili kondisi individu yang benar dan salah. Dan kemudian kondisi ini berlaku untuk semua elemen array yang sesuai. Rumus mengonversi argumen False dan True masing-masing menjadi 0 dan 1, dan mengeluarkan larik di mana 1 adalah nilai yang cocok yang ditemukan dalam string. MATCH() akan menemukan nilai pertama yang cocok dengan 1 dan meneruskannya ke rumus INDEX(). Dan itu, pada gilirannya, akan mengembalikan nilai yang sudah diinginkan di baris yang ditentukan dari kolom yang diinginkan.

Rumus tanpa larik bergantung pada kemampuan INDEX() untuk menanganinya sendiri. INDEX() kedua dalam rumus cocok dengan falsy (0), sehingga melewati seluruh array dengan nilai-nilai tersebut ke rumus MATCH(). 

Ini adalah penjelasan yang agak panjang tentang logika di balik formula ini. Untuk informasi lebih lanjut baca artikel “PERTANDINGAN INDEKS dengan beberapa kondisi'.

RATA-RATA, MAX dan MIN di INDEX dan MATCH

Excel memiliki fungsi khusus untuk menemukan rata-rata, maksimum, dan minimum. Tetapi bagaimana jika Anda ingin mendapatkan data dari sel yang terkait dengan nilai tersebut? Pada kasus ini AVERAGE, MAX dan MIN harus digunakan bersamaan dengan INDEX dan MATCH.

INDEX MATCH dan MAX

Untuk mencari nilai terbesar di kolom D dan menampilkannya di kolom C, gunakan rumus: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

PERTANDINGAN INDEKS dan MIN

Untuk mencari nilai terkecil di kolom D dan menampilkannya di kolom C, gunakan rumus berikut:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

INDEKS PENCARIAN dan ULAR

Untuk menemukan nilai rata-rata di kolom D dan menampilkan nilai ini di C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Bergantung pada bagaimana data Anda ditulis, argumen ketiga untuk MATCH() adalah 1, 0, atau -1:

• jika kolom diurutkan dalam urutan menaik, atur 1 (maka rumus akan menghitung nilai maksimum, yang kurang dari atau sama dengan nilai rata-rata);
• jika pengurutannya menurun, maka -1 (rumus akan menampilkan nilai minimum yang lebih besar dari atau sama dengan rata-rata);
• jika array pencarian berisi nilai yang sama persis dengan rata-rata, maka setel ke 0. 

 Dalam contoh kami, populasi diurutkan dalam urutan menurun, jadi kami menempatkan -1. Dan hasilnya adalah Tokyo, karena nilai populasi (13,189) paling dekat dengan nilai rata-rata (000).

VLOOKUP() juga dapat melakukan perhitungan seperti itu, tetapi hanya sebagai rumus array: VLOOKUP dengan AVERAGE, MIN dan MAX.

INDEX MATCH dan ESND/IFERROR

Anda mungkin sudah memperhatikan bahwa jika rumus tidak dapat menemukan nilai yang diinginkan, itu akan menimbulkan kesalahan # N / A. Anda dapat mengganti pesan kesalahan standar dengan sesuatu yang lebih informatif. Misalnya, atur argumen dalam rumus Pada tanggal XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Dengan rumus ini, jika Anda memasukkan data yang tidak ada dalam tabel, formulir akan memberi Anda pesan yang ditentukan.

Jika Anda ingin menangkap semua kesalahan, maka kecuali untuk Pada tanggal XNUMX dapat digunakan KESALAHAN:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), “Ada yang tidak beres!”)

Tetapi ingat bahwa menutupi kesalahan dengan cara ini bukanlah ide yang baik, karena kesalahan standar melaporkan pelanggaran dalam rumus.

Kami harap panduan kami untuk menggunakan fungsi INDEX MATCH() bermanfaat bagi Anda.