Memahami Rumus Vlookup Dengan Studi Kasus

Cara gampang memahami fungsi dan rumus VLOOKUP di excel ialah dengan cara mempraktekannya secara pribadi untuk memecahkan kasus kasus olah data excel. Dalam postingan kali ini, saya mengajak pembaca untuk mengupas beberapa pola penerapan fungsi VLOOKUP di dalam rumus excel.

Namun Sebelum membaca penggalan ini, diperlukan anda sudah memahami dasar-dasar penggunaan fungsi VLOOKUP terlebih dahulu.

Jika anda masih gres memakai fungsi ini dan belum memahami bagaimana cara kerjanya, silahkan untuk membaca terlebih dahulu artikel tutorial penggalan pertama : VLOOKUP – Rumus Jitu Yang Harus Dikuasai Pengguna Excel

Secara garis besar, bahasan berguru excel kali ini adalah:

Rumus VLOOKUP Excel dengan dua kriteria atau criteria ganda
Rumus VLOOKUP untuk Mendapatkan keberadaan nilai pertama, kedua, ketiga dan Seterusnya
VLOOKUP dua dimensi

Rumus VLOOKUP Excel dengan Kriteria Ganda

Kita sudah mengenal kecanggihan rumus VLOOKUP untuk mencari nilai tertentu dalam sebuah database dan mengexplorasi data lainnya yang terkait dengan nilai tersebut.

Namun sebagaimana kita ketahui, Fungsi ini hanya mengizinkan satu kriteria nilai yang dicari.

Sedangkan dalam prakteknya, kita sering menemukan kasus dimana ada beberapa kondisi dengan aneka macam criteria yang harus dilihat dan diobservasi lebih lanjut.

Contoh VLOOKUP dengan 2 Criteria

Misalnya : Di sebuah perkebunan, kita ingin mengetahui berapa sih hasil panen dari masing – masing blok panen per tenaga kerja pemanen? .

Dengan kata lain, kita mempunyai 2 kriteria yang harus diperhatikan untuk mencari nilai angka hasil panen.

Criteria yang dimaksud adalah:

Nama Blok
Nama Pemanen

Perhatikan gambaran berikut:

Kita sudah mengetahui bahwa VLOOKUP hanya sanggup membaca satu kolom pertama untuk pencarian nilai (sebuah kriteria) dan mendapat nilai dari kolom lainnya dari sebuah tabel.

Sementara dalam pola kasus ini, kita mempunyai 2 kriteria.

Lantas bagaimana solusinya?

Solusinya ialah mengakibatkan 2 kriteria menjadi 1 kriteria dengan cara menggabungkannya.

Dalam pola kasus ini kita perlu menggabungkan nama blok dan nama pemanen di dalam sebuah kolom bantu.

Dalam gambaran di atas, saya sudah mempersiapkan sebuah kolom kosong (kolom A) untuk dijadikan sebagai kolom bantu.

Pada sel A2 , Ketikan rumus berikut =C2&D2
Kemudian copy rumus dari sel A2 ke baris berikutnya hingga dengan baris terakhir tabel.
Pada sel H4, ketikan rumus berikut =VLOOKUP(H2&H3,A:E,5,0)
Hasilnya kita mendapat data nilai hasil panen sesuai gambaran berikut:

Referensi Keseluruhan Data Dalam Kolom (Entire Column)

Perhatikan kembali rumus =VLOOKUP(H2&H3,A:E,5,0)

Untuk parameter table_array, saya memakai acuan A:E.

Lho.. ko kenapa hanya kolom, tidak ada barisnya?.

Memang sengaja, alasannya pada prakteknya, penggunaan fungsi VLOOKUP sering melibatkan data yang sangat banyak, ribuan, puluhan ribu, bahkan hingga batas kemampuan jumlah baris yang disediakan oleh excel.

Jadi, A:E artinya semua data yang ada pada kolom A hingga kolom E, dari baris pertama hingga baris terakhir lembar kerja excel (entire column).

Ini penting semoga jikalau ada penambahan data pada baris selanjutnya, kita tidak perlu merubah acuan dalam rumus VLOOKUP.

Ada sih, cara lain untuk acuan tabel, yaitu dengan memakai data tabel (excel 2007 ke atas) atau data list (excel 2003). Tapi bukan disini pembahasannya semoga tidak rancu dengan istilah data table. Dan lupakan saja paragraf ini semoga tidak membingungkan ... :-)

VLOOKUP 2 Kriteria dari Sheet yang Berbeda

Jika anda sudah terbiasa dengan excel. Kemungkinan besar anda harus bekerja dengan beberapa sheet. Termasuk dalam memakai rumus VLOOKUP.

Jadi bagaimana caranya melaksanakan VLOOKUP kriteria ganda antar sheet.

Sebenarnya caranya sama saja. Hanya saja acuan tabel_array nya mengikat pada sheet tertentu.

Misalnya tabel array terletak dalam sheet berjulukan data, maka rumus =VLOOKUP(H2&H3,A:E,5,0) menjadi =VLOOKUP(H2&H3,data!A:E,5,0)

VLOOKUP dengan dengan 3 kriteria atau Lebih

Dengan cara yang sama kita juga bisa menciptakan VLOOKUP dengan lebih dari dua kriteria.

Trik nya simple saja

Buat kolom bantu pada penggalan paling kiri tabel
Isi kolom bantu tersebut dengan rumus untuk menggabungkan kriteria yang masih terpisah di kolom lainnya. Bisa dengan rumus memakai operator & (ampersand) ataupun memakai fungsi CONCATENATE
Buat rumus VLOOKUP dimana parameter lookup_value merupakan penggabungan beberapa kriteria yang sama dengan penggabungan pada kolom bantu.
Jika belum faham, silahkan baca kembali ke penggalan atas yaitu VLOOKUP dengan dua criteria. Karena berapapun kriteria yang digunakan, caranya sama... :-) hanya tinggal modifikasi rumus pada kolom bantu dan rumus VLOOKUP –nya.

Sekali lagi : Perlu diingat seputar penggunaan VLOOKUP dengan criteria ganda

Penggunaan rumus VLOOKUP untuk mencari dengan kriteria ganda harus melibatkan sebuah kolom bantu pada tabel_array
Kolom bantu harus terletak pada penggalan kiri dari sebuah tabel, kecuali jikalau memakai rumus adonan VLOOKUP dan CHOOSE dimana kita bisa meletakan kolom bantu di sembarang posisi. Silahkan baca kembali artikel VLOOKUP penggalan pertama jikalau anda belum memahami dasar-dasar VLOOKUP dari kanan ke kiri.
Kolom bantu harus berisi nilai unik. Nilai unik ini berupa adonan text kriteria yang ditetapkan
Untuk menggabungkan criteria sanggup memakai fungsi CONCATENATE atau operator &. Silahkan dipilih mana yang lebih gampang dan nyaman bagi anda. Kalau saya sendiri biasanya memakai operator &
Rumus CONCATENATE sanggup dituliskan dengan syntak L

=CONCATENATE(tex1, tex1, text3,...)

Menggabungkan text dengan operator & sanggup dituliskan dengan syntak:

=text1&text2&text3&...

Tanda titik 3 (...), baik dalam syntax CONCATENATE maupun operator &, artinya kita sanggup menambahkan text berikutnya sesuai kebutuhan.
Misalnya: jikalau kita menginginkan data dengan tanggal, blok dan pemanen (3 kriteria) maka kita harus menciptakan rumus yang sanggup diilustrasikan sebagai berikut:

Rumus pada kolom bantu:

=CONCATENATE(tanggal,blok,pemanen) atau tanggal&blok&pemanen

Rumus VLOOKUP:

=VLOOKUP(CONCATENATE(tanggal_diketahui,blok_diketahui,pemanen_diketahui),tabel_array,nomor_kolom,0)

Atau

=VLOOKUP(tanggal_diketahui&blok_diketahui&pemanen_diketahui),tabel_array,nomor_kolom,0)

Rumus VLOOKUP untuk Mendapatkan keberadaan nilai pertama, kedua, ketiga dan Seterusnya

Sebagaimana kita ketahui bahwa fungsi VLOOKUP akan menghasilkan nilai dari kriteria yang posisinya ditemui paling atas dalam sebuah tabel. Sehingga jikalau ada criteria atau nama yang sama dalam table, maka kita tidak bisa mengharapkan nilai yang lain selain yang dijumpai pertama pada table.

Namun dengan sedikit modifikasi pada kolom bantu, kita sanggup menemukan nilai hasil panen pekerja panen yang sama sesuai nomor urut kemunculan nama tersebut dalam tabel.

Bagaimana caranya? Gunakan rumus COUNTIF pada kolom bantu untuk menambahkan nomor urut kemunculan pada nama pemanen.

Menggunakan tabel yang sama ibarat pola sebelumnya, Ketikan rumus pada sel A2 =D2&COUNTIF($D$2:D2;D2) , lalu copy rumus tersebut ke baris berikutnya pada kolom bantu (A)

Kemudian modifikasi label dan data kriteria yang diketahui

Sel G2 = Nama Pemanen
Sel G3 = Nomor urut
Sel G4 = Hasil Panen
Sel H2 = Isi dengan Nama pemanen yang akan dicari hasil panennya
Sel H3 = Isi dengan Nomor urut kemunculan nama pemanen dalam tabel
Sel H4 = Tuliskan rumus =VLOOKUP(H2&H3,A:E,5,0)

Untuk lebih jelasanya, perhatikan gambaran berikut:

VLOOKUP Dua Dimensi

Melakukan lookup 2 dimensi artinya ialah mencari nilai tertentu dengan didasarkan pada kriteria 2 arah yang terletak pada kolom dan baris. Kata lainnya ialah mencari nilai yang merupakan pertemuan antara baris dan kolom tertentu.

Mari kita telaah lebih jauh penerapan VLOOKUP 2 dimensi ini dalam kasus handling data excel.

Misalnya kita mempunyai sekumpulan data dengan struktur baris dan kolom yang menggambarkan data hasil panen per pemanen per bulan.

Nama pemanen disusun dalam struktur baris. Nama bulan disusun dalam struktur kolom.

Pertanyaanya: Bagaimana caranya untuk mendapat data hasil panen seorang pemanen pada bulan tertentu?

Perhatikan gambaran berikut.

Sebelum melaksanakan pencarian data memakai rumus, mari coba lakukan secara manual, dengan mata kepala.

Nama pemanen dan bulan sudah diketahui yaitu Ogah pada bulan April
Dengan mata kepala kita mencari hasil panen si Ogah di dalam tabel dengan menelusuri baris Ogah dan Berhenti pada kolom April
Akhirnya diketahui bahwa hasil panen si Ogah pada bulan April ialah 13.500
Bagaimana cara kerja otak kita untuk mendapat angka tersebut?
Caranya ialah melihat intersepsi atau garis temu antara baris nama pemanen (Ogah) dan Kolom nama bulan (Apr)

Selanjutnya mari kita cari penyelesaiannya dengan logika excel

Nama pemanen dan nama bulan yang sudah diketahui terletak pada sel E10 dan E11
Tabel terletak pada range B2:N8
Nama pemanen terletak pada penggalan tabel penggalan kiri, ini cocok untuk penggunaan rumus VLOOKUP
Secara umum, rumus untuk mendapat angka hasil panen si Ogah adalah

=VLOOKUP(E10,B2:N8,nomor_kolom,0)

Perhatikan parameter nomor_kolom. Ini menandakan nomor urut kolom dalam tabel.
Pertanyaannya: Bagaimana caranya untuk mendapat nomur urut tabel yang sesuai dengan nama bulan yang ditentukan.
Ini sanggup diatasi memakai rumus MATCH
Fungsi MATCH dipakai untuk mencari nomor urut nilai atau text tertentu baik dalam baris maupun kolom
Dalam pola kasus ini dipakai untuk mencari nomor urut bulan tertentu dalam struktur kolom
Secara umum, rumus untuk mendapat nomor urut memakai fungsi MATCH adalah

=MATCH(text,range,lingkup)

Text dalam pola kasus ini ialah nama bulan yang terletak dalam sel E11. Range ialah lokasi dalam struktur baris maupun kolom dimana salah satu sel dalam range tersebut berisi text yang dicari, range dimaksud dalam pola ialah B2:N2. Lingkup tidak usah dipertanyakan ketika ini, Isi saja 0 atau FALSE.
Jadi rumus untuk mencari nomor urut bulan sanggup dituliskan sebagai berikut

=MATCH(E11,B2:N2,0)

Rumus tersebut sanggup digabung dengan rumus VLOOKUP menggantikan parameter nomor_kolom, sehingga rumusnya menjadi:

=VLOOKUP(E10,B2:N8,MATCH(E11,B2:N2,0),0)

Sampai pada tahap ini, kita sudah berhasil melaksanakan VLOOKUP 2 dimensi.

Alternative lain Lookup 2 Dimensi

Selain memakai rumus VLOOKUP MATCH, juga ada beberapa rumus yang sanggup dijadikan sebagai alternative untuk melaksanakan Lookup 2 dimensi.

Berikut ialah contoh-contohnya, namun hanya untuk sebagai citra saja bahwa “Banyak Jalan Menuju Roma”. Untuk detailnya insya Alloh akan dibahas dalam kesempatan lain.

Menggunakan gabugan fungsi HLOOKUP dan MATCH

=HLOOKUP(E11,B2:N8,MATCH(E10,B2:B8,0),0)

Menggunakan fungsi SUMPRODUCT

=SUMPRODUCT((B3:B8=E10)*(C2:N2=E11),C3:N8)

Menggunakan adonan fungsi INDEX dan MATCH

=INDEX(C3:N8,MATCH(E10,B3:B8,0),MATCH(E11,C2:N2,0))

Menggunakan adonan fungsi OFFSET dan MATCH

=OFFSET(B2,MATCH(E10,B3:B8,0),MATCH(E11,C2:N2,0))

Menggunakan adonan fungsi INDIRECT, ADDRESS, ROW, COLUMN dan MATCH

=INDIRECT(ADDRESS(ROW(B2)+MATCH(E10,B3:B8,0),COLUMN(B2)+MATCH(E11,C2:N2,0)))

Menggunakan fungsi SUM dalam rumus array, tekan CTR + SHIFT + ENTER sehabis mengetik rumus berikut:

=SUM((B3:B8=E10)*(C2:N2=E11)*C3:N8)

Alternative Lookup 2 Dimensi memakai nama range dan operator spasi (operator intersepsi)

Untuk alternative cara ini saya ambil heading khusus alasannya ini pengetahuan gres bagi saya. Terus terang belum terfikir sebelumnya mengenai adanya operator intersepsi. Dan cara ini ternyata sangat sederhana dan mudah.

Kembali ke contoh: Seleksi tabel B2:N8
Buat nama range menurut kolom dan baris

Untuk excel 2007,2010,2013 dan 2016 dengan cara masuk dalam tab Formulas, lalu klik Create from. Berikut ilustrasinya memakai excel 2007

Bagi yang masih memakai excel 2003. Perintah Create name ini bisa diakses dari menu Insert--> Name-->Create

Setelah muncul obrolan box create name, pilih Top Row dan Left Column --> lalu tekan OK

Maka excel akan meng-create nama range menurut label kolom dan baris. Bisa dicek hasilnya pada name box yang terletak pada penggalan atas lembar kerja excel sebelah kiri.

Setelah itu kita sanggup dengan gampang menciptakan rumus menurut label kolom dan baris

Contohnya:

=ogah apr (hasil Ogah pada bulan Apr)

=unyil mar (hasil Unyil pada bulan Mar)

=kinoy sep (hasil Kinoy pada bulan Sep)

Jika menginginkan rumus yang lebih dinamis, sanggup dibantu memakai fungsi INDIRECT, contohnya dikarenakan nama pemanen dan nama bulan yang dicari berada di sel E10 dan E11, maka rumus sanggup dituliskan sebagai berikut =INDIRECT(E10) INDIRECT(E11)

Perhatikan ruang kosong (spasi) antara nama range (misal ogah apr). Tanda spasi tersebut merupakan operator excel untuk mendapat intersepsi atau garis temu antara duah buah range.

Demikian juga tanda spasi tersebut berguna yang sama jikalau dipakai diatara 2 fungsi INDIRECT.

Walah-walah mungkin bahasan di atas yang terakhir agak jauh dari fokus pembahasan mengenai rumus VLOOKUP yach..

Tapi tak apalah yang penting kita sanggup lebih memahami aneka macam alternative rumus untuk tujuan yang sama. Pada alhasil terserah anda mau memakai cara yang mana.

Cukup disini dulu ya..
Belajar Excel ! Excellent
Salam...

Baca Juga Tutorial Belajar Excel Lainnya