Cara Mengambil Data dari Sheet Lain dengan Kriteria di Excel (VLOOKUP, XLOOKUP, INDEX MATCH)

Mengelola data di Excel sering melibatkan lebih dari satu sheet. Data penjualan bisa ada di Sheet1, sedangkan laporan atau rekapnya ada di Sheet2 atau Dashboard. Untungnya, Excel menyediakan berbagai rumus untuk mengambil data dari sheet lain secara otomatis berdasarkan kriteria tertentu.

Artikel ini memberikan panduan lengkap mulai dari dasar referensi sheet, penggunaan VLOOKUP, XLOOKUP, INDEX MATCH, hingga FILTER Function untuk menghasilkan laporan dinamis.

Dasar Pengambilan Data Antar Sheet di Excel

Sebelum masuk ke rumus yang rumit, kita harus paham cara Excel “berbicara” antar sheet.

Cara Mereferensikan Sheet Lain

Untuk memberi tahu Excel agar melihat ke sheet yang berbeda, kita menggunakan format yang sangat sederhana, yaitu nama sheet diikuti dengan tanda seru (!).

Cara umum mengambil data dari sheet lain:

=Sheet1!A1

Jika nama sheet mengandung spasi:

='Data Penjualan'!A1

Tips Penamaan Sheet (Best Practice)

Ini adalah tips penting yang menunjukkan pengalaman (Experience) dan akan menyelamatkan Anda dari pusing di kemudian hari:

1. Gunakan Nama Pendek & Jelas: Ganti nama Sheet1 menjadi DataGaji atau MasterProduk.
2. HINDARI SPASI: Jika nama sheet Anda mengandung spasi (misal: Data Penjualan), Excel akan otomatis menambahkan tanda kutip tunggal (') di rumusnya, seperti ='Data Penjualan'!A1. Ini merepotkan.
3. Ganti Spasi dengan Underscore: Gunakan Data_Penjualan sebagai gantinya. Ini jauh lebih bersih dan aman untuk formula.
4. Absolute Reference
   • Untuk mengunci cell tertentu: =$A$1
   • Ini penting saat kita menyeret formula.

Menggunakan VLOOKUP untuk Mengambil Data dari Sheet Lain

VLOOKUP adalah rumus klasik Excel untuk mengambil data berdasarkan kriteria tertentu.

1. Rumus VLOOKUP Antar Sheet

=VLOOKUP(A2, Sheet1!A2:D100, 3, FALSE)

=VLOOKUP(A2, Sheet1!A2:D100, 3, FALSE)

Penjelasan:

• A2 = nilai yang dicari
• Sheet1!A2:D100 = range pencarian
• 3 = kolom ke-3
• FALSE = pencarian exact match

2. Contoh Kasus

Di Sheet2, kita ingin menampilkan Harga berdasarkan Kode Produk:

=VLOOKUP(A2, Sheet1!A2:C100, 3, FALSE)

=VLOOKUP(A2, Sheet1!A2:C100, 3, FALSE)

3. Untuk Mengatasi Error

Jika hasil #N/A:

Gunakan :

=IFERROR(VLOOKUP(...), "Tidak ditemukan")

=IFERROR(VLOOKUP(...), "Tidak ditemukan")

Mengambil Data Dengan Kriteria: VLOOKUP + IF

Untuk mengambil data hanya jika memenuhi syarat tertentu, kita bisa menambahkan fungsi IF.

1. Contoh Lookup Dengan Kriteria

Misalnya kita hanya ingin menampilkan harga jika Produk adalah “Laptop”:

=IF(A2="Laptop", VLOOKUP(A2, Sheet1!A2:C100, 3, FALSE), "")

=IF(A2="Laptop", VLOOKUP(A2, Sheet1!A2:C100, 3, FALSE), "")

2. Kegunaan

Teknik ini cocok untuk:

• Menampilkan data berdasarkan kategori
• Menarik data berdasarkan status tertentu
• Filtering sederhana tanpa FILTER Function

Menggunakan XLOOKUP untuk Mengambil Data dari Sheet Lain

XLOOKUP adalah fungsi modern yang lebih fleksibel dibanding VLOOKUP.

1. Rumus XLOOKUP Antar Sheet

=XLOOKUP(A2, Sheet1!A2:A100, Sheet1!C2:C100)

=XLOOKUP(A2, Sheet1!A2:A100, Sheet1!C2:C100)

Kelebihan:

• Bisa mencari kanan → kiri
• Tidak perlu col_index
• Bisa menampilkan default jika tidak ditemukan

2. Contoh Kriteria

=XLOOKUP("Laptop", Sheet1!B2:B100, Sheet1!C2:C100, "Tidak ada")

=XLOOKUP("Laptop", Sheet1!B2:B100, Sheet1!C2:C100, "Tidak ada")

3. Lebih Kuat dari VLOOKUP

• Range bisa lebih fleksibel
• Tidak error jika kolom berubah
• Bisa retur banyak kolom sekaligus (dynamic array)

Temukan juga artikel terkait cara mengelompokkan umur di excel

Menggunakan INDEX MATCH untuk Kriteria Lebih Fleksibel

INDEX MATCH adalah alternatif populer untuk profesional Excel karena fleksibel dan aman untuk dataset besar.

1. Rumus INDEX MATCH Antar Sheet

=INDEX(Sheet1!C2:C100, MATCH(A2, Sheet1!A2:A100, 0))

=INDEX(Sheet1!C2:C100, MATCH(A2, Sheet1!A2:A100, 0))

2. Keunggulan

• Bisa mengarah ke kiri (VLOOKUP tidak bisa)
• Cocok untuk data dengan banyak kolom
• Lebih aman untuk file besar

3. Contoh Multi-Kriteria Dasar

=INDEX(Sheet1!C2:C100, MATCH(1, (Sheet1!A2:A100=A2)*(Sheet1!B2:B100=B2), 0))

=INDEX(Sheet1!C2:C100, MATCH(1, (Sheet1!A2:A100=A2)*(Sheet1!B2:B100=B2), 0))

Mengambil Banyak Data Sekaligus dengan FILTER Function

FILTER Function adalah cara paling modern dan powerful untuk mengambil data dalam jumlah banyak dari sheet lain.

1. Rumus FILTER

=FILTER(Sheet1!A2:C100, Sheet1!B2:B100="Aktif")

=FILTER(Sheet1!A2:C100, Sheet1!B2:B100="Aktif")

2. Contoh Kasus

Menampilkan semua data pelanggan yang statusnya “Aktif”.

3. Multi-Kriteria

=FILTER(Sheet1!A2:C100, (Sheet1!B2:B100="Aktif")*(Sheet1!C2:C100="Bandung"))

=FILTER(Sheet1!A2:C100, (Sheet1!B2:B100="Aktif")*(Sheet1!C2:C100="Bandung"))

4. FILTER + SORT

=SORT(FILTER(Sheet1!A2:C100, Sheet1!B2:B100="Aktif"), 2, TRUE)

=SORT(FILTER(Sheet1!A2:C100, Sheet1!B2:B100="Aktif"), 2, TRUE)

Contoh Studi Kasus Lengkap

Mari kita gabungkan semua. Bayangkan kita memiliki dua sheet.

• Sheet1 (DataPenjualan): Berisi data transaksi mentah.

• Sheet2 (Laporan): Kita ingin membuat laporan dinamis.

• Di sel B2, kita ketik nama Produk (kriteria 1).
• Di sel B3, kita ketik nama Kota (kriteria 2).
• Di sel A6, kita ingin menampilkan semua transaksi yang cocok secara otomatis.

Rumus di sel A6 (Laporan): Gunakan FILTER dengan kriteria ganda:

=FILTER(DataPenjualan!A2:D9, (DataPenjualan!B2:B9 = B2) * (DataPenjualan!C2:C9 = B3), "Data Tidak Ditemukan")

=FILTER(DataPenjualan!A2:D9, (DataPenjualan!B2:B9 = B2) * (DataPenjualan!C2:C9 = B3), "Data Tidak Ditemukan")

• DataPenjualan!A2:D9: Rentang data yang ingin ditarik (kita kunci dari baris 2-9).
• (DataPenjualan!B2:B9 = B2): Kriteria 1 (Kolom Produk di DataPenjualan harus cocok dengan sel B2 di Laporan).
• (DataPenjualan!C2:C9 = B3): Kriteria 2 (Kolom Kota di DataPenjualan harus cocok dengan sel B3 di Laporan).
• *: Tanda bintang (*) berfungsi sebagai logika “DAN” (AND) dalam kriteria FILTER.

Hasil Akhir (Otomatis): Tabel di Laporan akan otomatis terisi dengan semua data penjualan “Buku Tulis” di “Bandung”.

Jika Anda ganti sel B2 menjadi “Pensil” dan B3 menjadi “Jakarta”, tabel hasil di A6-D9 akan otomatis berubah tanpa perlu mengubah rumus.

Tips dan Best Practices

Kesimpulan

Untuk mengambil data dari sheet lain dengan kriteria di Excel, ada banyak metode yang dapat digunakan:

• VLOOKUP → klasik & sederhana
• XLOOKUP → modern, fleksibel, powerful
• INDEX MATCH → paling fleksibel
• FILTER Function → terbaik untuk multi-hasil

Setiap rumus memiliki keunggulan sendiri, dan memilih rumus terbaik tergantung kebutuhan dan struktur data.

Referensi : Kelas Excel