Excel merupakan salah satu aplikasi yang paling sering digunakan di dunia kerja, terutama di bidang keuangan, akuntansi, dan data analisis. Terkadang, kita perlu mencocokkan dua set data dalam Excel untuk mendapatkan data yang lebih spesifik atau untuk menghilangkan data yang redundan. Dalam artikel ini, kita akan membahas berbagai cara untuk mencocokkan data di Excel beserta contoh penggunaannya.
Cara Mencocokkan Data di Excel dengan VLOOKUP
Cara yang paling umum digunakan untuk mencocokkan data di Excel adalah menggunakan formula VLOOKUP. Formula ini akan mengambil data dari sebuah tabel atau range dalam satu sheet Excel dan mencari data yang ingin dicocokkan dalam range lainnya. Berikut adalah sintaks dari formula VLOOKUP:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Penjelasan dari masing-masing argumen di atas adalah sebagai berikut:
- lookup_value: nilai yang ingin dicocokkan
- table_array: range data yang ingin dicocokkan
- col_index_num: nomor kolom dalam range data yang ingin diambil
- range_lookup: nilai yang menyatakan apakah hasil pencocokan harus persis atau tidak
Sebagai contoh, mari kita mencocokkan data dari dua tabel di bawah ini:
| Nama | Harga |
|---|---|
| Apel | 5000 |
| Pisang | 3000 |
| Mangga | 8000 |
Tabel 1
| Nama | Stok |
|---|---|
| Apel | 10 |
| Pisang | 20 |
| Jeruk | 5 |
Tabel 2
Dalam contoh ini, kita ingin mencocokkan harga dari setiap buah dalam Tabel 1 dengan stoknya dalam Tabel 2. Begini cara melakukannya:
- Buatlah kolom baru di sebelah kanan Tabel 2 dan beri judul “Harga”.
- Di sel pertama di bawah judul Harga, masukkan formula VLOOKUP berikut:
=VLOOKUP(A2, Tabel1, 2, FALSE) - Salin formula tersebut ke sel lainnya di kolom Harga.
- Hasilnya akan seperti Tabel 3 di bawah ini:
| Nama | Stok | Harga |
|---|---|---|
| Apel | 10 | 5000 |
| Pisang | 20 | 3000 |
| Jeruk | 5 | #N/A |
Tabel 3
Perlu diingat bahwa VLOOKUP hanya mencocokkan data dari kiri ke kanan. Jadi, jika tabel yang ingin dicocokkan memiliki kolom yang tidak terurut, perlu dilakukan pengurutan terlebih dahulu sebelum menggunakan VLOOKUP. Jika kita ingin mencocokkan data dari kanan ke kiri, maka perlu menggunakan formula HLOOKUP.
Cara Mencocokkan Data di Excel dengan INDEX dan MATCH
Selain menggunakan VLOOKUP dan HLOOKUP, kita juga bisa mencocokkan data di Excel dengan menggunakan formula kombinasi INDEX dan MATCH. Formula ini juga memiliki kelebihan karena bisa mencocokkan data dari atas ke bawah atau dari kiri ke kanan tanpa perlu melakukan pengurutan. Berikut adalah sintaks dari formula INDEX dan MATCH:
INDEX(array, MATCH(lookup_value, lookup_array, [match_type]), [column_num])
Penjelasan dari masing-masing argumen di atas adalah sebagai berikut:
- array: range data yang ingin dicocokkan
- lookup_value: nilai yang ingin dicocokkan
- lookup_array: range data yang ingin dibandingkan
- match_type: nilai yang menyatakan apakah hasil pencocokan harus persis atau tidak
- column_num: nomor kolom dalam range data yang ingin diambil (hanya diperlukan jika array mengandung lebih dari satu kolom)
Sebagai contoh, mari kita mencocokkan data dari dua tabel di bawah ini:
| Bulan | Pendapatan |
|---|---|
| Januari | 10000 |
| Februari | 12000 |
| Maret | 15000 |
Tabel 4
| Bulan | Konversi Rate |
|---|---|
| Januari | 0.1 |
| Maret | 0.2 |
| April | 0.15 |
Tabel 5
Dalam contoh ini, kita ingin mencocokkan pendapatan bulanan dari Tabel 4 dengan konversi rate dari Tabel 5. Begini cara melakukannya:
- Buatlah sebuah tabel baru di luar Tabel 4 dan Tabel 5 yang berisi bulan, pendapatan, dan konversi rate. Beri judul tabel tersebut “Pendapatan Tahunan”.
- Di sel pertama di bawah judul Pendapatan dalam tabel baru, masukkan formula INDEX dan MATCH berikut:
=INDEX(Tabel4[pendapatan], MATCH([@bulan], Tabel4[bulan], 0)) * INDEX(Tabel5[konversi_rate], MATCH([@bulan], Tabel5[bulan], 0)) - Salin formula tersebut ke sel lainnya di kolom Pendapatan dalam tabel baru.
- Hasilnya akan seperti Tabel 6 di bawah ini:
| Bulan | Pendapatan | Konversi Rate |
|---|---|---|
| Januari | 1000 | 0.1 |
| Februari | #N/A | #N/A |
| Maret | 3000 | 0.2 |
| April | #N/A | 0.15 |
Tabel 6
Formula INDEX dan MATCH cukup fleksibel dan bisa digunakan untuk mencocokkan data yang lebih kompleks. Namun, formula ini memerlukan sedikit lebih banyak pengetahuan dan waktu pembelajaran dibanding VLOOKUP dan HLOOKUP.
FAQ
1. Apakah ada cara lain untuk mencocokkan data di Excel selain dengan VLOOKUP dan INDEX MATCH?
Ya, ada beberapa cara lain untuk mencocokkan data di Excel, tergantung pada kebutuhan dan kompleksitas data. Beberapa contoh lainnya adalah:
- Cara manual: Cocokkan data secara manual dengan cara membandingkan setiap baris dalam dua tabel yang berbeda.
- PivotTable: Buat PivotTable dari dua set data dan cocokkan data dengan memfilter dan mengurutkan tabel.
- Power Query: Menggunakan Power Query untuk menggabungkan dan mencocokkan data dari berbagai sumber.
2. Apakah ada tutorial Excel yang lebih mendalam untuk mencocokkan data?
Ya, ada banyak sumber daya online yang bisa membantu Anda memahami cara mencocokkan data di Excel secara lebih mendalam. Beberapa sumber daya yang direkomendasikan adalah:
- Kursus Excel online, seperti di Udemy, Coursera, atau LinkedIn Learning.
- Buku Excel, seperti Microsoft Excel 2019 Step by Step atau Excel Data Analysis and Visualization
- Forum atau komunitas Excel, seperti pivot-table.com atau mrexcel.com
Video Tutorial: Cara Mencocokkan Data di Excel
Untuk melihat tutorial cara mencocokkan data di Excel secara visual, Anda bisa melihat video tutorial berikut:
