Fungsi VLOOKUP mempunyai beberapa kelemahan, salah satunya adalah fungsi VLOOKUP hanya akan mengembalikan nilai dari data yang pertama kali ditemukan didalam table_array. Jika ada beberapa data yang sama dalam table_array maka data ke-2, ke-3 dst tidak akan pernah bisa ditampilkan oleh fungsi VLOOKUP.
Misal ada data seperti gambar dibawah.
Ada nama orang tua dan nama anak. Satu orang tua mempunyai beberapa anak. Urutan anak sesuai dengan urutan pencatatannya.
Pertanyaannya siapakan nama anak ke-2 “Ahmad”?
Fungsi apakah yang akan digunakan untuk menyelesaikan pertanyaan ini?
Fungsi VLOOKUP tidak bisa digunakan untuk menyelesaikan pertanyaan ini, karena hasil fungsi VLOOKUP akan selalu menghasilkan nama anak pertama, karena memang inilah kelemahan dari fungsi VLOOKUP.
Solusi dengan fungsi COUNTIF dan kolom bantuan
Untuk bisa menghasilkan nama anak ke-2, ke-3 dst harus dibantu dengan kolom bantuan dan fungsi COUNTIF. Trik ini untuk menyiasati kelemahan fungsi VLOOKUP.
Kolom bantuan akan berisi data unik baru yang memungkinkan fungsi VLOOKUP untuk menampilkan data ke-2, ke-3 yang ditemukan. Kolom bantuan berisi nama orang tua yang digabung dengan data berapa kali nama orang tua tersebut muncul.
Untuk lebih jelasnya lihat gambar dibawah.
Cell A4 berisi nama “Ahmad” dan angka 1, artinya nama “Ahmad” dicell B4 baru pertama kali muncul.
Cell A8 berisi nama “Ahmad” dan angka 2, artinya nama “Ahmad” dicell B8 adalah nama “Ahmad” kedua yang muncul, setelah nama “Ahmad” dicell B4.
Cell A9 berisi nama “Ahmad” dan angka 3, artinya nama “Ahmad” dicell B9 adalah nama “Ahmad” ketiga yang muncul, setelah nama “Ahmad” dicell B4 dan B8.
dst…
Formula yang digunakan dikolom bantuan adalah sebagai berikut:
=B2&”|”&COUNTIF($B$2:B2,B2)
Perhatikan parameter range fungsi COUNTIF. Awal range menggunakan absolute reference dan akhir range menggunakan relative reference, dengan cara tersebut bisa menghitung berapa kali nama orang tua muncul pada posisi masing-masing nama.
Ketik formula tersebut dicell A2 kemudian kopi dan paste dirange A3:A10.
Hasilnya seperti gambar kedua diatas.
Setelah kolom bantuan berhasil dibuat, waktunya merangkai fungsi VLOOKUP.
Letakkan kursor dicell F4.
Ketik formula untuk fungsi VLOOKUP
Parameter lookup_value diisi dengan gabungan nama orangtua dan anak ke- berapa yang ingin dicari. Formula yang dihasilkan menjadi seperti berikut:
=VLOOKUP(F1&”|”&F2
Parameter table_array diisi dengan range A2:C10
Parameter col_index_num diisi dengan angka 3
Parameter range_lookup diisi dengan nilai FALSE
Formula yang dihasilkan menjadi seperti berikut:
=VLOOKUP(F1&”|”&F2,A2:C10,3,FALSE)
Jika sudah tekan tombol ENTER. Hasilnya seperti gambar dibawah.
Nama anak kedua “Ahmad” adalah “Khalid Ahmad”.
Dengan sedikit trik dan kolom bantuan kelemahan fungsi VLOOKUP bisa dihilangkan 😉
