Rumus VLOOKUP dikenal sangat berguna untuk mencari data di excel dalam beberapa skenario, termasuk diantaranya dalam hal vlookup data dengan 2 kriteria. Contoh-contoh rumus excel VLOOKUP pada pembahasan yang sudah-sudah hanya menggunakan fungsi VLOOKUP untuk mencari data dengan satu kriteria saja. Nah dalam kesempatan ini akan dibahas bagaimana menggunakan VLOOKUP untuk mencari data dengan 2 kondisi atau lebih.
Sebelum melangkah ke contoh soal, alangkah baiknya jika kita simak video contoh rumus excel berikut ini supaya anda mendapatkan gambaran yang lebih jelas mengenai bagaimana cara membuat rumus VLOOKUP 2 KRITERIA
Contoh soal:
Sudah tersedia sebuah tabel terdiri atas 3 kolom. Kolom ke-1 berisi data nama produk buah-buahan, kolom ke-2 berisi data bulan penjualan dan kolom ke-3 berisi data jumlah penjumlahan dengan satuan Kg. Pada bagian lain disediakan form input pencarian data. Data buah dan bulan diinput secara manual, sedangkan jumlah penjualan harus dirumuskan dengan merujuk pada nama buah dan bulan yang sudah diketahui.
Gambaran contoh soal excel ini dapat dilihat dalam screenshot di bawah ini:
Bagaimana rumus nya untuk mencari data yang tepat sesuai 2 data lainnya yang sudah diketahui tersebut?
Kita sebut saja 2 data lainnya yang sudah diketahui tersebut dengan sebutan 2 kriteria atau 2 kondisi.
Sebagaimana kita ketahui sebelumnya bahwa VLOOKUP selalu menjadikan kolom pertama dari sebuah tabel sebagai referensi untuk mencari posisi kriteria yang sudah diketahui dalam tabel tersebut. Jika ada 2 item yang sama dalam list data, maka VLOOKUP hanya akan memberikan informasi item yang sama yang ditemukan paling atas.
Misalnya: perhatikan kolom Nama produk dalam tabel contoh. Jika VLOOKUP digunakan untuk mencari informasi seputar produk apel, maka informasi yang didapatkan hanya untuk produk apel pada bulan Januari saja, karena posisinya paling atas diantara item apel lainnya. Padahal kita mungkin ingin mengetahui informasi perihal penjualan produk apel di bulan Februari atau Maret.
Sebagai solusi atas permasalahan tersebut, maka kita perlu modifikasi rumus VLOOKUP sehingga dapat bekerja untuk 2 kriteria. Untuk itu, diperlukan sebuah kolom bantu yang digunakan untuk menyimpan informasi gabungan dari dua kriteria tersebut.
Disamping sebelah kiri tabel contoh sudah saya sediakan sebuah kolom kosong (kolom A) yang dapat digunakan sebagai kolom bantu.
Ketik rumus =B2&C2 pada sel A2, kemudian copy rumus tersebut sampai baris akhir data. Hasil dari rumus tersebut adalah gabungan text nama buah dan nama bulan yang diperoleh dari kolom B dan kolom C. Dengan adanya tambahan 1 kolom bantu di sebelah kiri tabel tersebut, maka tabel menjadi terdiri atas 4 kolom, dengan urutan
- Kolom ke-1 = Kolom bantu
- Kolom ke-2 = Nama Produk
- Kolom ke-3 = Bulan
- Kolom ke-4 = Jumlah Penjualan.
Tabel tersebut terdapat pada range A1:D10
Selanjutnya kita ingin menggunakan rumus VLOOKUP untuk mencari data dengan dua kriteria / kondisi.
- Kriteria 1 ⇒ Nama buah = Apel
- Kriteria 2 ⇒ Nama bulan = Februari
Secara umum rumus VLOOKUP 2 kriteria dapat dituliskan sebagai berikut:
=VLOOKUP(kriteria1&kriteria2, tabelPlusKolBantu, nomorKolomDicari, 0)
- Mengenai kriteria1 dan kriteria2 sudah dijelaskan diatas (contoh kriteria1= "Apel", Kriteria2= "Februari")
- tabelPlusKolBantu artinya referensi range berupa tabel yang sudah dilengkapi kolom bantu di kolom paling kiri, berisi data gabungan dari 2 kriteria yang dicari. (contoh: range A1:D10)
- nomorKolomDicari maksudnya adalah nomor urut kolom data dimana kita menginginkan data tersebut sesuai dua kriteria yang diketahui (contoh : jumlah penjualan = kolom ke-4)
Selanjutnya dengan mengganti parameter rumus dengan data sesuai contoh maka rumus di atas dapat kita rubah menjadi:
=VLOOKUP("Apel"&"Februari",A2:D10,4,0)
Hasilnya adalah 500, yaitu jumlah penjualan buah apel pada bulan Februari.
Karena sel input cari nama buah terletak di sel H2, dan nama bulan di sel H3, maka rumus tersebut juga bisa kita rubah lagi menjadi:
=VLOOKUP(H2&H3,A2:D10,4,0)
Seperti terlihat dalam gambar di bawah ini:
Silahkan dicoba dengan merubah nama buah dan nama bulan dan lihat hasilnya. Kita akan mendapatkan data jumlah penjualan buah tertentu dan bulan tertentu yang sesuai.
Catatan: selain menggunakan operator ampersand (&)untuk menggabungkan 2 kriteria baik di tabel bantu maupun dalam rumus VLOOKUP, kita juga bisa menggunakan fungsi CONCATENATE. Sehingga rumus terakhir di atas dapat diganti menjadi:
=VLOOKUP(CONCATENATE(H2,H3),A2:D10,4,0).
Bagaimana jika ada 2 kriteria atau lebih?
Untuk mencari data dengan lebih dari 2 kriteria maka kita hanya perlu sedikit merubah rumus, baik rumus pada kolom bantu maupun rumus VLOOKUP nya sendiri.
- Rumus pada kolom bantu: =kriteria1&kriteria2&kriteria3&…
- Rumus VLOOKUP: =VLOOKUP(kriteria1&kriteria2&kriteria3&…, tabelPlusKolBantu, nomorKolomDicari, 0)
Tanda titik3 … silahkan diisi sesuai dengan jumlah kriteria yang dipertimbangkan dala pencarian data.
Sampai disini pembahasan singkat mengenai contoh rumus excel VLOOKUP untuk mencari data dengan 2 kriteria atau lebih. Harapannya penjelasan dalam artikel ini dapat difahami dengan mudah.
Terimakasih.
Artikel terkait.
- Rumus VLOOKUP Untuk Apa Sich?
- Rumus VLOOKUP Untuk Tanggal
- Rumus VLOOKUP Untuk Pengelompokan Nilai Ujian
- Rumus VLOOKUP Untuk Mencari Nama Barang
sebenarnya bisa saja menggunakan rumus Lookup saja sehingga tidak memerlukan penambahan kolom bantu. yaitu contohnya dengan data tabel diatas bisa pakai : LOOKUP(2,1/($B$2:$B$10=H2)/($C$2:$C$10=H3),($D$2:$D$10))
ReplyDelete