Rumus VLOOKUP Untuk Dua Hasil Pencarian Atau Lebih Dari Satu Tabel Dalam Excel
Rumus VLOOKUP Untuk 2 Hasil Pencarian - Rumus VLOOKUP memang cukup banyak variasinya.
Variasi ini pasti masih terkait dengan hasil pencarian dari sebuah tabel dalam Excel.
Kali ini kita akan membahas tentang bagaimana cara menggunakan rumus VLOOKUP Excel untuk menampilkan hasil lebih dari satu.
Dari beberapa pembahasan sebelumnya memang rumus VLOOKUP ini lebih umum digunakan untuk satu data yang unik.
Hanya saja jika sedikit dimodifikasi maka kita juga bisa menampilkan beberapa hasil dari satu tabel yang sama.
Misalnya kita akan menampilkan penjualan atas nama sales A dan hasil dari rumus VLOOKUP tersebut ada 5 sesuai dengan tanggal dan transaksi pada tabel sumber data.
Kenapa saya sebutkan harus ada modifikasi ?
Karena dalam proses tersebut kita akan menambahkan tabel dummy sebagai acuan pengambilan data lebih adri 2 tersebut.
Rumus VLOOKUP Untuk 2 Hasil Pencarian Atau Lebih
Sebelum melanjutkan apakah pembaca sudah memahami cara penggunaan dari rumus VLOOKUP yang akan kita bahas dalam artikel ini.
Secara umum rumus VLOOKUP dalam Excel dapat digunakan untuk menampilkan isi dari sebuah tabel dengan kriteria yang sudah kita tentukan.
Proses pencarian data dengan rumus VLOOKUP ini membutuhkan satu kriteria yang muncul dalam tabel sumber data.
Baca Juga : Penggunaan dan Fungsi Rumus Excel VLOOKUP
Jika belum memahami cara penggunaan dari rumus VLOOKUP ini silahkan pelajari dalam artikel yang sudah kita bahas sebelumnya.
Dalam pembahasan artikel ini saya akan memberikan dua contoh rumus VLOOKUP untuk 2 hasil pencarian atau lebih.
Untuk lebih jelasnya mari kita bahas satu - persatu serta melihat contohnya dalam Excel.
Untuk cara yang pertama ini kita akan menggunakan bantuan dari rumus Excel COUNTIF.
Untuk contoh tampilan tabelnya adalah sebagai berikut :
Untuk menampilkan hasil pencarian lebih dari 2 dengan rumus VLOOKUP kita akan menambahkan kolom dummy pada tabel sumber data serta tabel hasil pencarian.
Dari contoh gambar diatas kita akan menampilkan hasil pencarian untuk nama sales Andi dan semua penjualan atas nama sales tersebut muncul didalam kolom G.
Jika nama sales diganti maka tampilan hasil pencarian akan menyesuaikan dengan jumlah data dari hasil pencarian dari tabel penjualan.
Untuk membuat VLOOKUP dengan 2 hasil seperti dalam contoh diatas langkah yang pertama adalah buatlah kolom dummy seperti contoh diatas kolom A.
Kolom dummy ini akan menampilkan hasil sesuai dengan jumlah data misalnya sales Andi dalam kolom ini yang muncul adalah Andi1, Andi2 dan Andi3.
Nama Andi1, Andi2 dan Andi3 ini akan menjadi kode pencarian yang kita lakukan dengan rumus VLOOKUP.
Jika pembaca masih ingat pola ini pernah saya gunakan dalam pembahasan tentang Nomor Urut Otomatis.
Dalam kolom dummy ini kita akan menggunakan rumus COUNTIF sebagai tambahan penghitungan jumlah nama yang muncul.
Rumus COUNTIF dalam Excel dapat digunakan untuk menghitung jumlah baris dengan kriteria tertentu.
Kriteria dalam kolom dummy ini adalah nama sales itu sendiri yang ada pada kolom A.
Rumus dalam kolom A cell A4 adalah sebagai berikut :
=B4&COUNTIF($B$4:B4;B4)
Rumus ini akan secara otomatis memunculkan nama sales serta urutan nomor pada nama tersebut.
Silahkan copy dan pastekan rumus tersebut dari cell A5 sampai dengan cell A10 seperti dalam contoh diatas.
Setelah membuat kolom dummy pada tabel sumber data maka langkah selanjutnya kita akan membuat kolom dummy pada tabel hasil pencarian.
Dalam contoh diatas kolom dummy yang kedua pada tabel hasil pencarian ada di kolom E.
Rumus dalam kolom dummy ini akan menggabungkan nomor urut dari kolom F dengan nama sales yang kita masukan kedalam cell G4.
Rumusnya adalah sebagai berikut :
=$G$4&F6
Hasil dari rumus ini jika nama sales yang kita masukan adalah Andi maka kolom dummy akan terisi Andi1, Andi2, Andi3 dan Andi4.
Sedangkan jika nama sales yang kita masukan adalah Tina maka hasilnya Tina1, Tina2, Tina3 dan Tina4.
Setelah kedua kolom dummy kita buat maka selanjutnya kita tinggal mengisi rumus VLOOKUP pada kolom G.
Rumus VLOOKUP pada kolom G ini adalah sebagai berikut :
=IFERROR(VLOOKUP(E6;A4:C10;3;0);0)
Hasilnya akan muncul jumlah penjualan sesuai dengan nama sales yang kita masukan kedalam cell G4.
Kenapa dalam rumus tersebut menambahkan rumus IFERROR ?
Jika hasil pencarian tidak ditemukan maka rumus VLOOKUP akan menampilkan kesalahan pada kolom G.
Rumus IFERROR tersebut akan menghilangkan pesan kesalahan atau error dari rumus VLOOKUP tersebut.
Dengan rumus - rumus tersebut data sudah tampil sesuai dengan hasil pencarian dan nama salesnya.
Hanya saja menurut saya ada yang kurang enak tampilannya dalam tabel hasil pencarian.
Apa hal yang kurang enak tersebut ?
Perhatikan, berapapun jumlah data yang muncul maka nomor urut dalam kolom F akan selalu muncul.
Rasanya tampilan tersebut akan menjadi lebih baik jika nomor urut yang muncul sesuai dengan jumlah data yang muncul hasil dari pencarian dengan rumus VLOOKUP.
Untuk kondisi tersebut mari kita membahasnya dalam cara VLOOKUP untuk dua hasil pencarian yang ke-2.
Sebenarnya cara yang kedua ini hampir sama dengan cara yang pertama hanya saja kita akan modifikasi nomor urut yang ada dalam kolom F.
Tampilan dari contoh yang kedua ini adalah sebagai berikut :
Dalam gambar diatas terlihat bahwa jika hasil pencarian hanya ada 2 maka nomor urut 3 dan 4 tidak akan muncul.
Ini tentu berbeda dengan cara yang pertama karena dalam cara yang pertama berapapun hasil pencarian nomor urut 1-4 akan tetap muncul.
Untuk langkah yang pertama pembuatan kolom dummy dan rumus pada kolm A ini sama dengan cara yang pertama dan saya tidak akan bahas lagi.
Langkah yang kedua kita akan membuat kolom dummy pada tabel hasil pencarian.
Rumus yang digunakan dalam kolom dummy yang kedua ini adalah sebagai berikut :
=IF(F6<>"";$G$4&F6;"-")
Rumus tersebut hanya akan menampilkan nomor urut jika nomor pada kolom F sudah ada.
Untuk cara penggunaan rumus IF rasanya pembaca sudah bisa menggunakannya karena rumus ini cukup populer dikalangan pengguna Excel.
Selanjutnya mari kita buat rumus untuk kolom nomor urut pada hasil pencarian atau kolom F.
Dalam kolom F ini kita akan menggunakan rumus Excel ROWS sebagai penghitung jumlah barisnya.
Seperti yang kita ketahui bahwa dalam Excel rumus ROWS ini dapat digunakan untuk menghitung jumlah baris meskipun dalam keadaan kosong.
Inilah alasannya kenapa saya tidak menggunakan rumus COUNT karena rumus COUNT hanya akan menghitung jumlah baris yang ada isinya saja.
Rumus dalam kolom F tepatnya cell F6 adalah sebagai berikut :
=IF(AND(COUNTIF($B$4:$B$10;$G$4)>=1;
ROWS($F$6:F6)<=COUNTIF($B$4:$B$10;$G$4));
ROWS($F$6:F6);"")
Selanjutnya silahkan copy dan pastekan rumus tersebut dari mulai cell F7 sampai dengan cell F9.
Langkah selanjutnya saya menambahkan dropdown list pada cell G4 supaya tidak salah dalam pengetikan nama salesnya.
Ini hanya opsi saja dan jika tidak ingin maka silahkan dilewati karena tidak berpengaruh terhadap hasil pencarian.
Dropdown list dalam cell G4 ini tampilannya adalah sebagai berikut :
Ini tentu akan mempermudah kita dalam menampilkan hasil pencarian dan selain itu akan menghindari adanya kesalahan ketika pada nama sales.
Untuk membuat dropdown list seperti pada gambar diatas langkah - langkahnya adalah sebagai berikut :
1. Klik cell G4
2. Klik Tab Data
3. Klik Data Validation
4. Klik Data Validation
5. Dalam kotak Allow pilih List
6. Dalam kotak Source masukan : Andi;Rudi;Tina
7. Klik OK
Langkah yang terakhir kita akan menambahkan rumus VLOOKUP pada kolom G dan rumusnya sama dengan pada langkah yang pertama atau seperti berikut ini :
=IFERROR(VLOOKUP(E6;A4:C10;3;0);0)
Sampai dengan langkah ini seluruh proses pembuatan tabel hasil pencarian 2 atau lebih dengan rumus VLOOKUP sudah selesai dilakukan.
Silahkan ikuti langkah - langkah VLOOKUP untuk dua hasil pencarian diatas secara teliti.
Jika belum berhasil atau muncul error silahkan bandingkan dengan contoh file berikut ini :
Nama file : VLOOKUP 2 hasil
Ukuran : 23 kb
Link : contoh file
Jika setelah membandingkan dengan contoh file diatas tetapi masih belum berhasil silahkan sampaikan masalahnya melalui kolom komentar dibawah artikel ini.
Itulah pembahasan kita kali ini tentang VLOOKUP untuk 2 hasil pencarian atau lebih dan semoga artikel ini bermanfaat untuk semua pembaca.
Rumus VLOOKUP Untuk 2 Hasil Pencarian Cara ke-1
Untuk cara yang pertama ini kita akan menggunakan bantuan dari rumus Excel COUNTIF.
Untuk contoh tampilan tabelnya adalah sebagai berikut :
Untuk menampilkan hasil pencarian lebih dari 2 dengan rumus VLOOKUP kita akan menambahkan kolom dummy pada tabel sumber data serta tabel hasil pencarian.
Dari contoh gambar diatas kita akan menampilkan hasil pencarian untuk nama sales Andi dan semua penjualan atas nama sales tersebut muncul didalam kolom G.
Jika nama sales diganti maka tampilan hasil pencarian akan menyesuaikan dengan jumlah data dari hasil pencarian dari tabel penjualan.
Untuk membuat VLOOKUP dengan 2 hasil seperti dalam contoh diatas langkah yang pertama adalah buatlah kolom dummy seperti contoh diatas kolom A.
Kolom dummy ini akan menampilkan hasil sesuai dengan jumlah data misalnya sales Andi dalam kolom ini yang muncul adalah Andi1, Andi2 dan Andi3.
Nama Andi1, Andi2 dan Andi3 ini akan menjadi kode pencarian yang kita lakukan dengan rumus VLOOKUP.
Jika pembaca masih ingat pola ini pernah saya gunakan dalam pembahasan tentang Nomor Urut Otomatis.
Dalam kolom dummy ini kita akan menggunakan rumus COUNTIF sebagai tambahan penghitungan jumlah nama yang muncul.
Baca Juga : Cara Menggunakan Fungsi COUNTIF Dalam Microsoft Excel
Rumus COUNTIF dalam Excel dapat digunakan untuk menghitung jumlah baris dengan kriteria tertentu.
Kriteria dalam kolom dummy ini adalah nama sales itu sendiri yang ada pada kolom A.
Rumus dalam kolom A cell A4 adalah sebagai berikut :
=B4&COUNTIF($B$4:B4;B4)
Rumus ini akan secara otomatis memunculkan nama sales serta urutan nomor pada nama tersebut.
Silahkan copy dan pastekan rumus tersebut dari cell A5 sampai dengan cell A10 seperti dalam contoh diatas.
Setelah membuat kolom dummy pada tabel sumber data maka langkah selanjutnya kita akan membuat kolom dummy pada tabel hasil pencarian.
Dalam contoh diatas kolom dummy yang kedua pada tabel hasil pencarian ada di kolom E.
Rumus dalam kolom dummy ini akan menggabungkan nomor urut dari kolom F dengan nama sales yang kita masukan kedalam cell G4.
Rumusnya adalah sebagai berikut :
=$G$4&F6
Hasil dari rumus ini jika nama sales yang kita masukan adalah Andi maka kolom dummy akan terisi Andi1, Andi2, Andi3 dan Andi4.
Sedangkan jika nama sales yang kita masukan adalah Tina maka hasilnya Tina1, Tina2, Tina3 dan Tina4.
Setelah kedua kolom dummy kita buat maka selanjutnya kita tinggal mengisi rumus VLOOKUP pada kolom G.
Rumus VLOOKUP pada kolom G ini adalah sebagai berikut :
=IFERROR(VLOOKUP(E6;A4:C10;3;0);0)
Hasilnya akan muncul jumlah penjualan sesuai dengan nama sales yang kita masukan kedalam cell G4.
Kenapa dalam rumus tersebut menambahkan rumus IFERROR ?
Jika hasil pencarian tidak ditemukan maka rumus VLOOKUP akan menampilkan kesalahan pada kolom G.
Rumus IFERROR tersebut akan menghilangkan pesan kesalahan atau error dari rumus VLOOKUP tersebut.
Dengan rumus - rumus tersebut data sudah tampil sesuai dengan hasil pencarian dan nama salesnya.
Hanya saja menurut saya ada yang kurang enak tampilannya dalam tabel hasil pencarian.
Apa hal yang kurang enak tersebut ?
Perhatikan, berapapun jumlah data yang muncul maka nomor urut dalam kolom F akan selalu muncul.
Rasanya tampilan tersebut akan menjadi lebih baik jika nomor urut yang muncul sesuai dengan jumlah data yang muncul hasil dari pencarian dengan rumus VLOOKUP.
Untuk kondisi tersebut mari kita membahasnya dalam cara VLOOKUP untuk dua hasil pencarian yang ke-2.
Rumus VLOOKUP Untuk 2 Hasil Pencarian Cara ke-2
Sebenarnya cara yang kedua ini hampir sama dengan cara yang pertama hanya saja kita akan modifikasi nomor urut yang ada dalam kolom F.
Tampilan dari contoh yang kedua ini adalah sebagai berikut :
Dalam gambar diatas terlihat bahwa jika hasil pencarian hanya ada 2 maka nomor urut 3 dan 4 tidak akan muncul.
Ini tentu berbeda dengan cara yang pertama karena dalam cara yang pertama berapapun hasil pencarian nomor urut 1-4 akan tetap muncul.
Untuk langkah yang pertama pembuatan kolom dummy dan rumus pada kolm A ini sama dengan cara yang pertama dan saya tidak akan bahas lagi.
Langkah yang kedua kita akan membuat kolom dummy pada tabel hasil pencarian.
Rumus yang digunakan dalam kolom dummy yang kedua ini adalah sebagai berikut :
=IF(F6<>"";$G$4&F6;"-")
Rumus tersebut hanya akan menampilkan nomor urut jika nomor pada kolom F sudah ada.
Untuk cara penggunaan rumus IF rasanya pembaca sudah bisa menggunakannya karena rumus ini cukup populer dikalangan pengguna Excel.
Selanjutnya mari kita buat rumus untuk kolom nomor urut pada hasil pencarian atau kolom F.
Dalam kolom F ini kita akan menggunakan rumus Excel ROWS sebagai penghitung jumlah barisnya.
Baca Juga : Rumus ROWS Di Excel Untuk Menghitung Jumlah Baris
Seperti yang kita ketahui bahwa dalam Excel rumus ROWS ini dapat digunakan untuk menghitung jumlah baris meskipun dalam keadaan kosong.
Inilah alasannya kenapa saya tidak menggunakan rumus COUNT karena rumus COUNT hanya akan menghitung jumlah baris yang ada isinya saja.
Rumus dalam kolom F tepatnya cell F6 adalah sebagai berikut :
=IF(AND(COUNTIF($B$4:$B$10;$G$4)>=1;
ROWS($F$6:F6)<=COUNTIF($B$4:$B$10;$G$4));
ROWS($F$6:F6);"")
Selanjutnya silahkan copy dan pastekan rumus tersebut dari mulai cell F7 sampai dengan cell F9.
Langkah selanjutnya saya menambahkan dropdown list pada cell G4 supaya tidak salah dalam pengetikan nama salesnya.
Ini hanya opsi saja dan jika tidak ingin maka silahkan dilewati karena tidak berpengaruh terhadap hasil pencarian.
Dropdown list dalam cell G4 ini tampilannya adalah sebagai berikut :
Ini tentu akan mempermudah kita dalam menampilkan hasil pencarian dan selain itu akan menghindari adanya kesalahan ketika pada nama sales.
Untuk membuat dropdown list seperti pada gambar diatas langkah - langkahnya adalah sebagai berikut :
1. Klik cell G4
2. Klik Tab Data
3. Klik Data Validation
4. Klik Data Validation
5. Dalam kotak Allow pilih List
6. Dalam kotak Source masukan : Andi;Rudi;Tina
7. Klik OK
Langkah yang terakhir kita akan menambahkan rumus VLOOKUP pada kolom G dan rumusnya sama dengan pada langkah yang pertama atau seperti berikut ini :
=IFERROR(VLOOKUP(E6;A4:C10;3;0);0)
Sampai dengan langkah ini seluruh proses pembuatan tabel hasil pencarian 2 atau lebih dengan rumus VLOOKUP sudah selesai dilakukan.
Silahkan ikuti langkah - langkah VLOOKUP untuk dua hasil pencarian diatas secara teliti.
Jika belum berhasil atau muncul error silahkan bandingkan dengan contoh file berikut ini :
Nama file : VLOOKUP 2 hasil
Ukuran : 23 kb
Link : contoh file
Jika setelah membandingkan dengan contoh file diatas tetapi masih belum berhasil silahkan sampaikan masalahnya melalui kolom komentar dibawah artikel ini.
Itulah pembahasan kita kali ini tentang VLOOKUP untuk 2 hasil pencarian atau lebih dan semoga artikel ini bermanfaat untuk semua pembaca.
Post a Comment for "Rumus VLOOKUP Untuk Dua Hasil Pencarian Atau Lebih Dari Satu Tabel Dalam Excel"