Bagi Anda yang menguasai Excel , kemungkinan besar Anda sudah sangat familiar dengan fungsi VLOOKUP . Fungsi VLOOKUP digunakan untuk menemukan nilai dalam sel yang berbeda berdasarkan beberapa teks yang cocok dalam baris yang sama.

Jika Anda masih baru menggunakan fungsi VLOOKUP , Anda dapat melihat posting saya sebelumnya tentang cara menggunakan VLOOKUP di Excel^{(how to use VLOOKUP in Excel)} .

Sekuat apa pun, VLOOKUP memiliki batasan tentang bagaimana tabel referensi yang cocok perlu disusun agar formula berfungsi.

Artikel ini akan menunjukkan kepada Anda batasan di mana VLOOKUP tidak dapat digunakan dan memperkenalkan fungsi lain di Excel yang disebut INDEX-MATCH yang dapat mengatasi masalah tersebut.

INDEX MATCH Contoh Excel

Menggunakan contoh spreadsheet Excel berikut , kami memiliki daftar nama pemilik mobil dan nama mobil. Dalam contoh ini, kami akan mencoba mengambil  ID Mobil^{(Car ID)} berdasarkan Model Mobil^{(Car Model)} yang terdaftar di bawah beberapa pemilik seperti yang ditunjukkan di bawah ini:

Pada lembar terpisah bernama CarType , kami memiliki database mobil sederhana dengan  ID , Model Mobil^{(Car Model)} , dan Warna^(Color) .

Dengan pengaturan tabel ini,  fungsi VLOOKUP hanya dapat berfungsi jika data yang ingin kita ambil terletak pada kolom di sebelah kanan yang kita coba cocokkan ( bidang Model Mobil^{(Car Model )} ).

Dengan kata lain, dengan struktur tabel ini, karena kami mencoba mencocokkannya berdasarkan Model Mobil^{(Car Model)} , informasi yang kami dapatkan hanyalah Warna^{(Color )} (Bukan ID karena kolom ID  terletak di sebelah kiri  kolom Model Mobil^{(Car Model )} .)

Ini karena dengan VLOOKUP , nilai pencarian harus muncul di kolom pertama dan kolom pencarian harus di sebelah kanan. Tak satu pun dari kondisi tersebut terpenuhi dalam contoh kita.

Kabar baiknya adalah, INDEX-MATCH  akan dapat membantu kita dalam mencapai hal ini. Dalam praktiknya, ini sebenarnya menggabungkan dua fungsi Excel yang dapat bekerja secara individual: fungsi INDEX dan fungsi MATCH .

Namun, untuk tujuan artikel ini, kita hanya akan membicarakan kombinasi keduanya dengan tujuan mereplikasi fungsi VLOOKUP .

Rumusnya mungkin tampak agak panjang dan menakutkan pada awalnya. Namun, setelah Anda menggunakannya beberapa kali, Anda akan hafal sintaksnya.

Ini adalah rumus lengkap dalam contoh kita:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Berikut adalah rincian untuk setiap bagian

=INDEX( – Tanda “=” menunjukkan awal rumus dalam sel dan INDEX adalah bagian pertama dari fungsi Excel yang kita gunakan.

CarType!$A$2:$A$5 – kolom pada lembar CarType  tempat data yang ingin kita ambil berada. Dalam contoh ini, ID  dari setiap Model Mobil.^{(Car Model.)}

MATCH( – Bagian kedua dari fungsi Excel yang kita gunakan.

B4 – Sel yang berisi teks pencarian yang kita gunakan ( Model Mobil^{(Car Model)} ) .

CarType!$B$2:$B$5 – Kolom pada lembar CarType   dengan data yang akan kita gunakan untuk mencocokkan dengan teks pencarian.

0)) – Untuk menunjukkan bahwa teks pencarian harus sama persis dengan teks di kolom yang cocok (yaitu CarType!$B$2:$B$5 ). Jika kecocokan persis tidak ditemukan, rumus mengembalikan #N/A .

Catatan: ingat tanda kurung tutup ganda di akhir fungsi ini "))" dan koma di antara argumen.^{(Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.)}

Secara pribadi saya telah pindah dari VLOOKUP dan sekarang menggunakan INDEX-MATCH karena mampu melakukan lebih dari VLOOKUP .

Fungsi INDEX-MATCH juga memiliki manfaat lain dibandingkan dengan VLOOKUP :

1. Perhitungan Lebih Cepat^{(Faster Calculations)}

Saat kami bekerja dengan kumpulan data besar di mana penghitungannya sendiri dapat memakan waktu lama karena banyak fungsi VLOOKUP , Anda akan menemukan bahwa setelah Anda mengganti semua rumus tersebut dengan INDEX-MATCH , penghitungan keseluruhan akan dihitung lebih cepat.

1. Tidak Perlu Menghitung Kolom Relatif^{(No Need to Count Relative Columns)}

Jika tabel referensi kita memiliki teks kunci yang ingin kita cari di kolom C dan data yang kita perlukan ada di kolom AQ , kita perlu mengetahui/menghitung berapa banyak kolom antara kolom C dan kolom AQ saat menggunakan VLOOKUP .

Dengan fungsi  INDEX-MATCH , kita dapat langsung memilih kolom indeks (yaitu kolom AQ) di mana kita perlu mendapatkan data dan memilih kolom yang akan dicocokkan (yaitu kolom C).

1. Terlihat Lebih Rumit^{(It Looks More Complicated)}

VLOOKUP cukup umum saat ini, tetapi tidak banyak yang tahu tentang penggunaan fungsi INDEX-MATCH secara bersamaan.

String yang lebih panjang dalam fungsi INDEX-MATCH membantu membuat Anda terlihat seperti ahli dalam menangani fungsi Excel yang kompleks dan canggih. ^(Excel)Menikmati!

When to Use Index-Match Instead of VLOOKUP in Excel

For those of you who are well-versed in Excel, you are most likely very familiar with the VLOOKUP function. The VLOOKUP function is used to find a value in a different cell based on some matching text within the same row.

If you are still new to VLOOKUP function, you can check out my previous post on how to use VLOOKUP in Excel.

As powerful as it is, VLOOKUP has a limitation on how the matching reference table needs to be structured in order for the formula to work.

This article will show you the limitation where VLOOKUP cannot be used and introduce another function in Excel called INDEX-MATCH that can solve the issue.

INDEX MATCH Excel Example

Using the following example Excel spreadsheet, we have a list of car owners name and the car name. In this example, we will be trying to grab the Car ID based on the Car Model listed under multiple owners as shown below:

On a separate sheet called CarType, we have a simple car database with the ID, Car Model and Color.

With this table setup, the VLOOKUP function can only work if the data that we want to retrieve is located on the column to the right of what we are trying to match (Car Model field).

In other words, with this table structure, since we are trying to match it based on the Car Model, the only information that we can get is Color (Not ID as the ID column is located to the left of the Car Model column.)

This is because with VLOOKUP, the lookup value must appear in the first column and the lookup columns have to be to the right. None of those conditions are met in our example.

The good news is, INDEX-MATCH will be able to help us in achieving this. In practice, this is actually combining two Excel functions that can work individually: INDEX function and MATCH function.

However, for the purpose of this article, we will only talk about the combination of the two with the aim of replicating the function of VLOOKUP.

The formula can seem to be a little bit long and intimidating at first. However, once you have used it several times, you will learn the syntax by heart.

This is the full formula in our example:

=INDEX(CarType!$A$2:$A$5,MATCH(B4,CarType!$B$2:$B$5,0))

Here is the breakdown for each section

=INDEX( – The “=” indicates the beginning of formula in the cell and INDEX is the first part of the Excel function that we are using.

CarType!$A$2:$A$5 – the columns on sheet CarType where the data we would like to retrieve is contained. In this example, the ID of each Car Model.

MATCH( – The second part of the Excel function that we are using.

B4 – The cell that contain search text that we are using (Car Model).

CarType!$B$2:$B$5 – The columns on sheet CarType  with the data which we will use to match against the search text.

0)) – To indicate that the search text has to exactly match with the text in the matching column (i.e. CarType!$B$2:$B$5). If the exact match is not found, the formula returns #N/A.

Note: remember the double closing bracket at the end of this function “))” and the commas between the arguments.

Personally I have moved away from VLOOKUP and now use INDEX-MATCH as it is capable of doing more than VLOOKUP.

The INDEX-MATCH functions also have other benefits as compared to VLOOKUP:

1. Faster Calculations

When we are working with large datasets where the calculation itself can take a long time due to many VLOOKUP functions, you will find that once you replace all of those formulas with INDEX-MATCH, the overall calculation will be compute faster.

1. No Need to Count Relative Columns

If our reference table is having the key text that we want to search in column C and the data that we need to get is in column AQ, we will need to know/count how many columns are between column C and column AQ when using VLOOKUP.

With the INDEX-MATCH functions, we can directly select the index column (i.e column AQ) where we need to get the data and select the column to be matched (i.e. column C).

1. It Looks More Complicated

VLOOKUP is quite common nowadays, but not many know about using the INDEX-MATCH functions together.

The longer string in INDEX-MATCH function help to make you look like an expert in handling complex and advanced Excel functions. Enjoy!

Related posts

• Cara Memperbaiki Kesalahan #N/A dalam Rumus Excel seperti VLOOKUP

• Cara Menghapus Baris Kosong di Excel

• Cara Menggunakan Fitur Sel Bicara Excel

• Cara Memasukkan Lembar Kerja Excel ke dalam Word Doc

• Cara Menggunakan Analisis Bagaimana-Jika Excel

• Cara Menulis Rumus/Pernyataan IF di Excel

• Cara Menggunakan Referensi Absolut di Excel

• Mengapa Anda Harus Menggunakan Rentang Bernama di Excel

• Cara Menghapus Garis Kisi di Excel

• Cara Membuat Makro atau Script VBA Di Excel

• 10 Tip dan Trik Excel untuk 2019

• Menghubungkan Excel ke MySQL

• Cara Mencari Di Excel

• Penyortiran Data Satu Kolom dan Multi-Kolom Dasar di Spreadsheet Excel

• Cara Menggunakan Pernyataan If dan Nested If di Excel

• Google Spreadsheet vs Microsoft Excel – Apa Perbedaannya?

• Cara Memasukkan CSV atau TSV Ke dalam Lembar Kerja Excel

• Cara Berbagi File Excel Untuk Kolaborasi Mudah

• Gunakan Keyboard untuk Mengubah Tinggi Baris dan Lebar Kolom di Excel

• 3 Cara Memisahkan Sel Di Excel