Rumus VLookup Dengan 2 Hasil Pencarian Atau Lebih Pada Microsoft Excel

Rumus VLookup Dengan 2 Hasil Pencarian Atau Lebih Pada Microsoft Excel

Rumus VLookup Dengan 2 Hasil Pencarian Atau Lebih Pada Microsoft Excel

Jika anda sudah biasa menggunakan Rumus VLookup dan Hlookup tentu sudah maklum bahwa Fungsi VLookup maupun HLookup hanya bisa menghasilkan 1 hasil pencarian dari sebuah tabel data, yakni sesuai data pertama yang ditemukan oleh Fungsi VLookup atau HLookup.

Jika kita ingin mendapatkan hasil pencarian kedua, ketiga dan seterusnya bagaimana rumus excelnya?

Untuk lebih memahami maksud hasil pencarian VLookup kedua, ketiga dan seterusnya silahkan perhatikan contoh gambar berikut:

VLookup Dengan 2 Hasil Pencarian

Dari contoh gambar di atas, kita ingin saat melakukan Lookup atau pencarian menggunakan fungsi VLookup untuk nama "Budi" Pada Tabel 1 maka setiap data dengan nama "Budi" bisa masuk ke Tabel ke-2.


Untuk pemecahan kasus soal Vlookup dengan 2 hasil semacam ini ada beberapa cara dan rumus excel yang bisa kita gunakan.

Selanjutnya mari kita belajar rumus excel untuk pemecahan kasus ini.


Untuk pemecahan kasus semacam ini setidaknya ada tiga cara atau rumus yang bisa kita gunakan. Cara pertama tetap menggunakan fungsi VLookup excel. Cara kedua adalah dengan menggunakan rumus array gabungan fungsi INDEX-IF-SMALL. Dan cara ketiga adalah dengan memanfaatkan fitur advanced filter pada microsoft excel.

Pada kesempatan ini akan kita bahas cara pertama dan kedua saja. Adapun cara ketiga meskipun tidak khusus membahas kasus semacam ini bisa anda pelajari pada laman berikut: Menampilkan Hasil Advanced Filter di Sheet Lain.

2 HASIL PENCARIAN DENGAN VLOOKUP


Sebenarnya Fungsi VLookup tidak dapat menampilkan 2 hasil pencarian, namun dengan trik tertentu hal ini menjadi mungkin dilakukan dengan fungsi VLookup. Namun sebelum itu, tentunya syarat pertama anda harus tahu dulu bagaimana menggunakan fungsi VLookup pada microsoft excel.

Untuk panduan rumus excel VLookup bisa anda pelajari pada bagian berikut : Cara menggunakan rumus excel Vlookup.

Jika ingin Vlookup mendapatkan 2 hasil atau lebih dari pencarian data maka kita butuh untuk membuat kolom dummy atau kolom bantu.

Langkah-langkah yang perlu kita lakukan adalah sebagai berikut:

Buatlah satu kolom bantu disebelah kiri tabel pertama. Kemudian gunakan rumus excel seperti berikut ini lalu copy-paste ke bawah:

=C3&COUNTIF($C$3:C3;C3)

Cara VLookup Dengan 2 Hasil Pencarian

Rumus diatas digunakan untuk menambahkan counter untuk setiap data yang ada pada kolom nama sehingga data tersebut menjadi unik/tidak ganda lagi pada kolom dummy (bantu).

Perhatikan bahwa pada fungsi countif ini kita menggunakan referensi semi absolut.

Setelah membuat kolom bantu di atas buat juga kolom bantu di sebelah kiri tabel kedua. Pada baris pertama kolom tabel tersebut masukkan rumus excel seperti dibawah ini, kemudian copy-paste ke sel lain dibawahnya sebanyak yang anda butuhkan:

=$H$2&G4

Sehingga hasilnya akan seperti contoh gambar di bawah ini:

Cara VLookup Untuk 2 Hasil Pencarian

Kolom inilah yang akan kita gunakan sebagai kunci pencarian untuk fungsi VLookup.

Kebetulan pada contoh ini ada kolom NO. yang bisa kita manfaatkan untuk menyusun counter nama, jika tidak ada anda bisa memanfaatkan fungsi ROW untuk mengisi kolom bantu ini. Sehingga rumus excel alternatif yang bisa anda gunakan adalah sebagi berikut:

=$H$2&ROW()-3

Atau

=$H$2&ROW(A1)

Ketiga rumus terakhir ini akan menghasilkan nilai yang sama pada kolom bantu. Jadi terserah rumus yang mana yang anda pilih. Intinya buat kolom bantu dengan counter 1,2,3 dan seterusnya sampai kemungkinan maksimal banyaknya data yang mungkin akan ditemukan oleh Vlookup.

Setelah dua kolom bantu di atas selesai anda buat maka tahap persiapan sudah cukup, berikutnya anda tinggal menerapkan rumus Vlookup seperti biasanya.

Pada baris pertama kolom setoran gunakan rumus VLookup seperti ini:

=VLOOKUP(F4;$A$2:$D$12;4;FALSE)

Kemudian kopi paste kebawah.

Rumus VLookup Untuk 2 Hasil Pencarian

Jika hanya ada tiga data pada tabel maka untuk baris ke-4 dan seterusnya akan menghasilkan nilai error #NA. Untuk menghilangkan nilai error #NA ini tambahkan fungsi IFERROR pada rumus di atas sehingga hasil akhirnya seperti berikut:

=IFERROR(VLOOKUP(F4;$A$2:$D$12;4;FALSE);"")

Untuk kolom Nama pada Tabel2, dengan sedikit penyesuaian anda bisa menggunakan rumus VLookup yang sama seperti apda contoh gambar di bawah:

Rumus VLookup Untuk 2 Hasil Pencarian Atau Lebih

Untuk selanjutnya anda bisa menyembunyiikan 2 kolom bantu di atas, agar Tabel data terlihat lebih bagus.

Pada kasus Vlookup ini, jika tidak menghendaki kolom bantu, adakah cara lainnya?

2 HASIL PENCARIAN DENGAN INDEX-IF-SMALL


Dengan cara VLookup, anda harus menggunakan kolom dummy agar untuk mendapatkan 2 hasil pencarian atau lebih. Jika tidak menghendaki adanya kolom bantu anda bisa menggunakan rumus array gabungan INDEX-IF-SMALL berikut ini.

=IFERROR(INDEX($D$3:$D$12;
SMALL(IF($C$3:$C$12=$H$2;
ROW($C$3:$C$12)-ROW($C$2));
ROW(A1)));"")

Akhiri penulisan rumus di atas dengan menekan Ctrl+Shift+Enter. Tanda {...} tidak ditulis manual, melainkan hasil otomatis setelah anda klik Ctrl+Shift+Enter secara bersama-sama setelah menuliskan rumus di atas.

VLookup 2 Hasil Pencarian Dengan INDEX-SMALL-IF Excel

Sesuaikan Array/referensi Index rumus pada baris pertama kolom nama pada Tabel 2 seperti berikut:

=IFERROR(INDEX($C$3:$C$12;
SMALL(IF($C$3:$C$12=$H$2;
ROW($C$3:$C$12)-ROW($C$2));
ROW(A1)));"")

Penjelasan untuk rumus ini akan saya bahas lain waktu.

Untuk masing-masing fungsi excel yang digunakan pada tutorial ini bisa anda pelajari pada laman-laman berikut:

  1. Fungsi Vlookup
  2. Fungsi Countif
  3. Fungsi Index
  4. Fungsi If
  5. Fungsi Small
  6. Fungsi Row
  7. Fungsi IFerror

0 komentar