Cara Mengambil Data Ke-N yang Cocok di Excel 2019 ke Bawah dengan VLOOKUP

Dalam pengolahan data di Excel, sering kali kita perlu mengambil data ke-n yang cocok di Excel berdasarkan kriteria tertentu. Hal ini berguna dalam berbagai situasi, seperti pencarian data pelanggan, analisis penjualan, atau pemrosesan informasi inventaris. Dengan mengambil data ke-n yang cocok di Excel, kita dapat memastikan bahwa informasi yang ditampilkan relevan dengan kebutuhan spesifik pengguna.

Salah satu cara efektif untuk mengambil data ke-n yang cocok di Excel 2019 dan versi sebelumnya adalah dengan menggunakan fungsi VLOOKUP. Metode ini memungkinkan kita untuk mencari entri tertentu dalam kumpulan data dan menampilkan hasil yang sesuai berdasarkan urutan kemunculannya.

Artikel ini akan membahas langkah-langkah detail dalam menggunakan metode cara mengambil data ke-n yang cocok di Excel ini, termasuk menerapkan rumus yang tepat, serta menyesuaikan formula agar dapat digunakan dalam berbagai skenario data. Dengan memahami teknik ini, Anda akan lebih mudah dalam mengelola data besar dan menemukan informasi yang diperlukan dengan efisien.

Mengapa Menggunakan VLOOKUP?

VLOOKUP adalah fungsi yang sangat berguna dalam pencarian data secara vertikal dalam tabel. Dengan sedikit modifikasi, kita bisa menggunakannya untuk mengambil data ke-n yang cocok di Excel, yaitu data yang sesuai dengan kriteria pencarian tertentu dalam urutan yang benar.

Menggunakan VLOOKUP untuk Mengambil Data ke-N yang Cocok

Sebagai contoh, di sini kita punya tabel data karyawan dari berbagai divisi yang dibedakan berdasarkan kode divisinya, dan kita ingin mencari dan mengambil data ke-n yang cocok di Excel yang berada di tabel berbeda berdasarkan kode divisinya. Berikut contoh tabelnya:

Untuk mengakomodir keperluan di atas, kita dapat menggunakan VLOOKUP untuk mencari data berdasarkan kriteria pencarian.

Misalnya, kita ingin mencari nama-nama karyawan berdasarkan kode divisi tertentu, yang ditentukan di sel H2 dan nomor urutnya di kolom G. Maka rumus yang digunakan untuk sel H5 adalah:

=VLOOKUP($H$2&"-"&$G5;$B$3:$E$11;COLUMN(B1);0)

Salin rumus di atas ke sel lainnya. Dan berikut adalah hasilnya:

Penjelasan rumus:

Sekarang, mari kita uraikan masing-masing bagian rumus:

a) $H$2&”-“&$G5
Bagian ini membentuk nilai pencarian unik dengan cara menggabungkan nilai dari H2 dan G5 menggunakan tanda hubung (-).

Contoh jika:

• H2 = “P”
• G5 = 1

Maka nilai yang dicari dalam tabel adalah “P-1”.

b) $B$3:$E$11
Ini adalah rentang tabel tempat pencarian dilakukan.

• Kolom pertama dalam rentang ini (kolom B) harus berisi nilai yang cocok dengan lookup_value (contohnya, “P-1”, “P-2”, dst.).
• Data yang ingin diambil berada di kolom selanjutnya dalam rentang ini.

c) COLUMN(B1)
Bagian ini menentukan nomor kolom yang akan diambil datanya.

• COLUMN(B1) bernilai 2, karena kolom B berada di posisi ke-2 relatif terhadap rentang.
• Jika rumus disalin ke kanan, maka COLUMN(B1), COLUMN(C1), COLUMN(D1) akan menghasilkan 2, 3, 4, yang secara otomatis mengambil data dari kolom ke-2, ke-3, dan ke-4 dari rentang $B$3:$E$11.

d) 0 (FALSE)
Parameter ini memastikan bahwa VLOOKUP hanya mengembalikan hasil yang cocok secara persis. Jika tidak ada yang cocok, rumus akan menghasilkan #N/A.

Cara Kerja Rumus dalam Praktik

Dilihat dari contoh tabel di atas, ketika kita memasukkan rumus VLOOKUP akan menghasilkan data sebagai berikut:

• H5 akan mengembalikan “Ani” karena “P-1” cocok dengan baris pertama.
• H6 akan mengembalikan “Dedi” karena “P-2” cocok dengan baris kedua.
• H7 akan mengembalikan “Wawan” karena “P-3” cocok dengan baris ketiga.

Demikian pula, rumus untuk kolom Divisi dan Gaji akan mengembalikan nilai secara berurutan.

Kelebihan dan Kekurangan Metode Ini

Kelebihan:

1. Cocok untuk Excel 2019 ke bawah karena tidak memerlukan fungsi modern seperti FILTER.
2. Mudah diterapkan dengan hanya menambahkan kolom bantu.
3. Dapat digunakan dengan data dinamis karena COUNTIF akan selalu memperbarui nomor urutan secara otomatis.

Kekurangan:

1. VLOOKUP hanya mencari dari kiri ke kanan, sehingga jika kolom pencarian berada di sebelah kanan data yang diambil, metode ini tidak akan berfungsi.
2. Tidak fleksibel untuk banyak kriteria, dibandingkan metode berbasis INDEX dan MATCH.

Kesimpulan

Mengambil data ke-n yang cocok di Excel dapat dilakukan dengan kombinasi VLOOKUP dan kolom bantu. Dengan metode ini, kita bisa mendapatkan data spesifik berdasarkan urutan kemunculannya. Cara ini sangat efektif di Excel 2019 ke bawah, terutama untuk dataset yang disusun secara vertikal. Jika menggunakan Excel versi terbaru, opsi seperti FILTER bisa menjadi alternatif yang lebih sederhana.

Dengan memahami teknik ini, Anda bisa lebih mudah mengolah dan mengambil data sesuai kebutuhan dalam pekerjaan sehari-hari.

Download Template Gratis

Bagi Anda yang ingin mencoba teknik ini secara langsung, kami telah menyiapkan template gratis yang dirancang khusus untuk membantu Anda mempaktikkan Cara Mengambil Data Ke-N yang Cocok di Excel 2019 ke Bawah dengan VLOOKUP ini. File Excel bisa didownload melalui tautan berikut:

Atau juga jika ingin mempelajari langkah demi langkah secara visual, bisa kunjungi Youtube Channel Depot Excel. Di sana teman-teman akan melihat bagaimana rumusnya bekerja. Semoga bermanfaat!