Rumus VLOOKUP Dinamis dengan Fungsi INDIRECT

Pencarian data dengan rumus VLOOKUP akan lebih powerfull jika dilengkapi tabel lookup  yang bersifat dinamis. Tabel lookup yang dinamis memungkinkan kita mengganti tabel rujukan tanpa perlu mengutak-atik rumus untuk merubah referensi. Cukup hanya dengan merubah data input maka informasi dari tabel yang sesuai dapat dengan cepat segera diperoleh. Untuk menjadikan tabel lookup bersifat dinamis, kita cukup menggabungkan fungsi VLOOKUP dengan fungsi INDIRECT.

Contoh Soal:

Anggaplah sudah tersedia dua buah tabel penjualan produk, masing-masing tabel terdiri atas 2 kolom. Tabel 1 berisi data penjualan di bulan Januari dan Tabel 2 berisi data penjualan di bulan Februari. Selanjutnya anda harus membuat rumus yang dapat mengambil data penjualan dari kedua tabel tersebut. Namun rumus tersebut nantinya harus fleksibel dan berlaku umum, dengan artian tidak perlu merubah referensi tabel lookup ketika rumus dicopy ke sel di bawahnya. Untuk lebih jelasnya, contoh soal excel ini dapat dilihat dalam gambar di bawah ini:

Sebelum melangkah ke contoh rumus VLOOKUP dengan tabel referensi yang bersifat dinamis, mari kita coba terlebih dahulu menggunakan rumus yang biasa. Misalnya di sel F3, kita ketikan rumus berikut untuk mencari nilai penjualan buah apel di bulan Januari.

=VLOOKUP(D3,$A$3:$B$5,2,0)

Jika rumus tersebut di copy ke sel di bawahnya (sel F4) maka rumus nya akan menjadi 

=VLOOKUP(D4,$A$3:$B$5,2,0)

Perhatikan parameter yang di-highlight kuning yaitu range  tabel lookup $A$3:$B$5. Supaya lebih mudah dibaca selanjutnya saya tulis A3:B5. Referensi ini ketika di copy ke sel dibawahnya masih sama dan mengacu pada tabel bulan Januari. Padahal kita menginginkan data di bulan Februari. 

Untuk mendapatkan hasil yang benar, tentu saja kita harus merubah referensi tersebut menjadi $A$9:$B$11 atau A9:B11 yang mengacu ke tabel di bulan Februari. Jika ada banyak tabel maka kita harus merubah secara manual setiap kali mencopy rumus. Cara ini mungkin tidak dikehendaki.

Solusi: Jadikan Tabel Lookup Dinamis Menggunakan INDIRECT.

Untuk menjadikan tabel dinamis, bisa saja di kolom E (kolom Bulan), langsung diketikan referensi A3:B5 untuk menggantikan Januari serta referensi A9:B11 untuk menggantikan Februari. Tetapi penggunaan kode referensi secara langsung akan menyulitkan memahami formula. Oleh karena itu sangat disarankan untuk membuat nama pada masing-masing tabel.

Misalnya: Tabel 1 kita nama “Januari”

Caranya seleksi range A3:B5 kemudian ketikan "Januari" (tanpa tanda kutip) pada kotak nama yang terletak di bagian sebelah kiri atas (disamping formula bar) 

Lakukan  proses yang sama pada tabel 2 (range A9:B11) dan beri nama "Februari"

Sampai tahap ini, kita sudah memberikan nama pada masing-masing tabel. Nama inilah yang pada tahap selanjutnya akan dijadikan referensi bagi fungsi INDIRECT.

Sehingga kita bisa mengetikan rumus berikut untuk mendapatkan data penjualan sesuai data produk dan bulan yang diinput pada kolom D dan kolom E

=VLOOKUP(D3,INDIRECT(E3),2,0)

Penjelasan Rumus:

Kita ketahui, secara umum rumus VLOOKUP dapat dituliskan sebagai berikut:

=VLOOKUP(kataKunci,TabelLookup,NomorKolomHasil,0)

Sehingga rumus =VLOOKUP(D3,INDIRECT(E3),2,0) dapat dijelaskan secara rinci melalui argumen rumus yang digunakan yaitu:

• kataKunci merupakan nilai yang diinput pada sel D3 (contoh “Apel”)
• tabelLookup diwakili oleh fungsi INDIRECT(E3). Rumus ini akan mereferensikan nama range yang diinput di sel E3. Jika diinput Januari, maka akan mereferensikan range yang bernama Januari
• nomorKolomHasil adalah nomor 2 yaitu nomor urut kolom data penjualan dalam tabel.

Dengan menggunakan bantuan fungsi INDIRECT ini maka jika kita merubah inputan nama bulan pada kolom E, maka hasil rumus akan mengikuti dan menyesuaikan dengan bulan yang sesuai. Rumus tersebut juga bisa dicopy ke sel dibawahnya dengan aman tanpa harus mengutak-atik referensi lagi untuk menghasilkan nilai yang benar.

Demikian pembahasan singkat mengenai penggunaan fungsi INDIRECT untuk menjadikan tabel dinamis dalam rumus pencarian data menggunakan VLOOKUP.

Contoh Rumus Excel Lainnya:

• Menggali Informasi Dengan Rumus INDEX MATCH
• Rumus VLOOKUP Untuk Membuat Kode Barang
• Rumus VLOOKUP Untuk Membaca Kode Barang
• Rumus VLOOKUP Ke Kiri

Rumus VLOOKUP Untuk Membuat Kode Barang

Masih ada kaitan dengan pembahasan sebelumnya mengenai contoh rumus untuk membaca code barang, dalam kesempatan ini kita akan membahas hal sebaliknya yaitu contoh rumus VLOOKUP untuk membuat Kode barang berdasarkan deskripsi yang sudah diketahui. Untuk keperluan ini kita akan gunakan fungsi VLOOKUP dibantu dengan fungsi CONCATENATE yang berguna untuk mengkombinasikan komponen kode barang menjadi satu kode. Fungsi CHOOSE diperlukan jika kolom kunci dalam tabel lookup tidak terletak pada posisi paling kiri.

Sebelum menginjak pada contoh soal, sebaiknya teman-teman terlebih dahulu memahami prinsip dasar bagaimana cara menggunakan fungsi VLOOKUP. mimin sudah menyiapkan video sederhana berikut ini:

Setelah memahami prinsip dasar fungsi VLOOKUP, baiklah mari kita menginjak pada contoh soal:

Contoh Soal.

Dalam hal ini kita akan gunakan contoh tabel seperti yang digunakan dalam pembahasan mengenai contoh rumus untuk membaca code barang. Format kode barangnya adalah XXY00 ,dimana:

• XX merupakan kode peruntukan pengguna pakaian apakah anak-anak (kode CC) atau dewasa (kode AC).
• Y merupakan kode yang menggambarkan type gender pengguna pakaian apakah laki-laki (kode M), Wanita (W), atau Umum (G).
• 00 merupakan kode yang code yang menggambarkan kisaran harga barang, yaitu:
• Harga dibawah 100 ribu (kode 01)
• Harga 100 ribu s.d 199 ribu (kode 10)
• Harga 200 ribu s.d 299 ribu (kode 20)
• Harga 300 ribu s.d 399 ribu (kode 30)
• Harge 400 ribu s.d 499 ribu (kode 40)
• Harga 500 ribu atau lebih (kode 50)

Gambaran contoh kasus dapat dilihat dalam gambar di bawah ini:

Contoh Rumus VLOOKUP untuk Membuat Kode Barang.

Dari gambar contoh soal di atas kita dapat menyusun kode yang dapat mengindentifikasi 3 deskripsi atau keterangan mengenai barang. Masing-masing deskripsi dapat kita rumuskan secara terpisah menggunakan fungsi VLOOKUP, kemudian hasilnya digabungkan menggunakan fungsi CONCATENATE.

Perhatikan pada kata kunci deskripsi 1, 2 dan 3  dan lihat pada kolom A dan B yang dijadikan rujukan tabel lookup. Karena kolom kata kunci di sebelah kanan, maka diperlukan bantuan fungsi CHOOSE.

Rumus General Komponen Kode  Deskripsi / Keterangan barang.

= VLOOKUP(deskripsi, CHOOSE({1,2}, rangeKolomDeskripsi, rangeKolomKode),2,0)

Contoh Rumus Kode Barang Deskripsi ke-1

Cell dan Range referensi yang digunakan sebagai argumen rumus

• Deskripsi = deskripsi ke-1 atsbarang yang sudah diketahui, contoh deskripsi diinput di sel F2
• rangeKolomDeskripsi = range kolom deskripsi pada tabel deskripsi kode 1 yaitu range B2:B3
• rangeKolomKode = range kolom kode pada tabel deskripsi kode 1 yaitu range A2:A3

Dengan menggunakan referensi tersebut maka rumus kode deskripsi ke-1 dapat ditulis sebagai berikut:

=VLOOKUP(F2,CHOOSE({1,2},B2:B3,A2:A3),2,0)

Contoh Rumus Kode Barang Deskripsi ke-2

Cell dan Range referensi yang digunakan sebagai argumen rumus keterangan ke-2

• Deskripsi = deskripsi ke-2 atas barang yang sudah diketahui, contoh deskripsi diinput di sel F3
• rangeKolomDeskripsi = range kolom deskripsi pada tabel deskripsi kode 2 yaitu range B6:B8
• rangeKolomKode = range kolom kode pada tabel deskripsi kode 1 yaitu range A6:A8

Selanjutnya rumus kode deskripsi ke-2 dapat ditulis sebagai berikut:

=VLOOKUP(F3,CHOOSE({1,2},B6:B8,A6:A8),2,0)

Contoh Rumus Kode Barang Deskripsi ke-3

Cell dan Range referensi yang digunakan sebagai argumen rumus keterangan ke-3 yaitu:

• Deskripsi = deskripsi ke-3 atas barang yang sudah diketahui, contoh deskripsi diinput di sel F4.
• rangeKolomDeskripsi = range kolom deskripsi pada tabel deskripsi kode 3 yaitu range B11:B16.
• rangeKolomKode = range kolom kode pada tabel deskripsi kode 1 yaitu range A11:A16.

Selanjutnya rumus kode deskripsi ke-1 dapat ditulis sebagai berikut:

=VLOOKUP(F4,CHOOSE({1,2},B11:B16,A11:A16),2,0)

Contoh Rumus Menggabungkan Komponen Kode Barang Deskripsi 1 s/d 3

Selanjutnya supaya kode barang tidak terpisah-pisah, maka kite perlu menggabungkannya menggunakan fungsi CONCATENATE. 

Rumus umum:

=CONCATENATE(rumusKode1, rumusKode2, rumusKode3)

Contoh Rumus

=CONCATENATE(VLOOKUP(F2,CHOOSE({1,2},B2:B3,A2:A3),2,0), VLOOKUP(F3,CHOOSE({1,2},B6:B8,A6:A8),2,0), VLOOKUP(F4,CHOOSE({1,2},B11:B16,A11:A16),2,0))

Hasilnya terlihat seperti dalam gambar di bawah ini:

Selain menggunakan fungsi CONCATENATE, kita juga bisa menggunakan operator & (ampersand) untuk menggabungkan elemen kode deskripsi barang, sehingga rumus di atas juga dapat dituliskan sebagai berikut:

=VLOOKUP(F2,CHOOSE({1,2},B2:B3,A2:A3),2,0) &  VLOOKUP(F3,CHOOSE({1,2},B6:B8,A6:A8),2,0) & VLOOKUP(F4,CHOOSE({1,2},B11:B16,A11:A16),2,0)

Catatan:

Fungsi CHOOSE dapat ditiadakan jika kolom deskripsi berada pada kolom pertama tabel kode deskripsi. Dengan demikian kita bisa membuat rumus seperti berikut:

=CONCATENATE(VLOOKUP(F2,A2:B3,2,0), VLOOKUP(F3,A6:B8,2,0), VLOOKUP(F4,A11:B16,2,0))

Atau 

=VLOOKUP(F2,A2:B3,2,0) & VLOOKUP(F3,A6:B8,2,0) & VLOOKUP(F4,A11:B16,2,0)

Contohnya dapat dilihat dalam sreenshot di bawah ini.

Dapat kita lihat dari gambar di atas, bahwa kolom deskripsi terletak pada kolom pertama (kolom A atau kolom paling kiri), sedangkan kolom kode berada pada kolom ke-2. Situasi ini paling sesuai dengan keperluan rumus VLOOKUP secara normal, sehingga tidak diperlukan bantuan fungsi CHOOSE.

Sampai disini pembahasan contoh rumus excel VLOOKUP untuk membuat kode barang. Mudah-mudahan bisa bermanfaat.

Terimakasih.

Contoh Rumus Excel Lainnya

• Rumus VLOOKUP untuk Membaca Kode Barang
• Rumus VLOOKUP Ke Kiri
• Rumus VLOOKUP 2 Kriteria
• Rumus VLOOKUP 2 Dimensi

Rumus VLOOKUP untuk Membaca Kode Barang

Kode barang sangat penting untuk mempermudah identifikasi suatu barang, misalnya barang dagangan. Dengan semakin banyaknya item barang dagangan, tentu saja si penjual perlu cara untuk mempermudah membaca, mengenali dan mengindentifikasi barang dagangannya. Setiap pedagang mungkin saja memiliki kode sendiri atas barang dagangannya.Pembahasan kali ini akan memperlihatkan contoh rumus excel sederhana untuk membaca kode barang yang juga pola kodenya sangat sederhana. Fungsi yang digunakan adalah VLOOKUP dikombinasikan dengan fungsi MID.

Sebelum melanjutkan pembahasan, alangkah baiknya teman-teman terlebih dahulu menyimak video singkat mengenai cara menggunakan fungsi VLOOKUP berikut ini:

Jika teman-teman sudah memahami prinsip dasar fungsi VLOOKUP seperti yang sudah dijelaskan dalam video di atas, maka saya yakin teman-teman akan lebih mudah memahami contoh soal VLOOKUP berikut:

Contoh Soal:

Kode barang dagangan sebuah toko pakaian terdiri atas 5 karakter. Kode ditulis dengan struktur XXY00, dibuat dengan maksud mempermudah identifikasi 3 deskripsi penting terkait barang dagangan di toko tersebut.

• Dua huruf pertama (XX) menunjukan deskripsi ke-1 berupa kategori umur pengguna pakaian apakah untuk orang dewasa atau anak-anak
• Kode CD = Pakaian anak-anak
• Kode AD = Pakaian dewasa

• Satu huruf ke-3 (Y)  menunjukan deskripsi ke-2 berupa jenis kelamin pengguna pakaian, apakah laki-laki, wanita atau umum.
• Kode M = Pakaian Pria
• Kode W = Pakaian Wanita
• Kode G  = Pakaian umum

• Dua angka ke terakhir (00) menunjukan deskripsi ke-3 berupa kisaran harga barang.
• Kode 00 = Harga di bawah 100 ribu
• Kode 10 = Harga 100 ribu s.d 199 ribu
• Kode 20 = Harga 200 ribu s.d 299 ribu
• Kode 30 = Harga 300 ribu s.d 399 ribu
• Kode 40 = Harga 400 ribu s.d 499 ribu
• Kode 50 = Harga 500 ribu lebih

Gambar soal dalam spreadsheet dapat dilihat dalam screenshot di bawah ini.

Contoh Rumus Untuk Membaca Code Deskripsi 1

Deskripsi 1 yaitu kategori umur pengguna pakaian apakah anak – anak ataukah dewasa. Deskripsi ini diwakili oleh 2 karakter pertama dari kode barang. Untuk mendapatkan 2 karakter pertama dari kode, kita bisa menggunakan bantuan MID. Rumusnya dapat dituliskan sebagai berikut:

=MID(kode,1,2)

Contoh:

=MID("ACM10",1,2) ⇒ Hasil = "AC"

Karena text kode yang akan dicek diletakan di sel F2, maka string kode dapat kita ganti dengan referensi sel F2, sehingga rumus ini menjadi:

=MID(F2,1,2) 

Selanjutnya tugas fungsi VLOOKUP untuk mengkonversi fragmen code tersebut menjadi deskripsi 1. Secara umum, dapat formula nya dapat dituliskan seperti ini:

=VLOOKUP(fragmenKode,tabelDeskripsi,2,0)

• fragmenKode merupakan 2 karakter pertama dari  text kode atau sesuai hasil rumus =MID(F2,1,2). 
• Tabel deskripsi merupakan referensi range deskripsi 1, dalam hal ini berlokasi pada range A2:B3.

Dengan menggunakan parameter-parameter tersebut pada rumus VLOOKUP, maka rumusnya menjadi:

=VLOOKUP(MID(F2,1,2),A2:B3,2,0)

Dengan cara yang serupa, selanjutnya kita juga bisa mencari deskripsi 2 dan 3 sesuai kode barang dengan sangat mudah.

Rumus Untuk Membaca Kode Deskripsi 2

Rumus UMUM

=VLOOKUP(fragmenKode,tabelDeskripsi,2,0)

• FragmenKode merupakan karakter ke-3 dari text kode barang yang dapat diperoleh menggunakan bantuan fungsi MID. Rumusnya yaitu =MID(kode,3,1)  atau sesuai contoh =MID(F2,3,1)
• TabelDeskripsi merupakan referensi range lokasi tabel deskripsi 2, yaitu range A6:B8

Sehingga rumus untuk membaca kode deskripsi ke-2 dapat dituliskan dengan rumus berikut ini:

=VLOOKUP(MID(F2,3,1),A6:B8,2,0)

Rumus Untuk Membaca Kode Deskripsi 3

Rumus UMUM

=VLOOKUP(fragmenKode,tabelDeskripsi,2,0)

• FragmenKode terdiri atas 2 karakter, yaitu karakter ke-4 dan ke-5 dari text kode barang. Fragmen kode ini dapat diperoleh menggunakan bantuan fungsi MID. Rumusnya yaitu =MID(kode,4,2)  atau sesuai contoh =MID(F2,4,2).
• TabelDeskripsi merupakan referensi range lokasi tabel deskripsi 3, yaitu range A11:B16

Sehingga rumus untuk membaca kode deskripsi ke-2 dapat dituliskan dengan rumus berikut ini:

=VLOOKUP(MID(F2,4,2),A11:B16,2,0)

Berikut screenshot penerapan rumus VLOOKUP yang dikombinasikan dengan rumus MID untuk membaca keterangan atas kode barang yang dibagi menjadi 3 deskripsi.

Sampai disini pembahasan contoh rumus VLOOKUP untuk membaca kode barang. Semoga bermanfaat.

Contoh rumus excel lainnya:

• Rumus VLOOKUP ke Kiri
• Rumus VLOOKUP 2 Dimensi
• Rumus VLOOKUP 2 Kriteria
• Rumus VLOOKUP Untuk Tanggal

Rumus VLOOKUP Ke Kiri

Rumus VLOOKUP dari kanan ke kiri? Apakah bisa? Umumnya fungsi VLOOKUP bekerja secara vertikal mencari nilai tertentu pada kolom kunci yang terletak paling kiri dari sebuah tabel. Setelah nilai ditemukan, kemudian bergerak dari kiri ke kanan untuk mendapatkan nilai dari kolom lainnya sesuai nomor urut kolom yang ditentukan, yang sebaris dengan posisi nilai ditemukan. Proses ini mewajibkan kolom kunci berada pada posisi paling kiri. Bagaimana seandainya kolom kunci posisinya bukan paling kiri? Apakah harus memindahkan kolom tersebut? Ternyata tidak perlu, karena dengan bantuan fungsi CHOOSE, kita bisa menjadikan fungsi VLOOKUP lebih fleksibel, dapat bekerja dari kanan ke kiri. 

Contoh Soal:

Sudah tersedia sebuah tabel berisi data nama siswa. Tabelnya sederhana saja, hanya terdiri atas 2 kolom. Kolom pertama (A) berisi nama siswa. Sedangkan kolom ke-2  (B) berisi Nomor Induk. Selanjutnya anda ditugaskan untuk melakukan pencarian nama siswa dengan kata kunci berupa Nomor Induk. Bagaimana anda merumuskan pencarian data tersebut menggunakan fungsi VLOOKUP?

Soal tersebut dapat digambarkan sebagai berikut:

Contoh rumus VLOOKUP untuk cari data dari kanan ke kiri.

Dengan melihat contoh kasus dan dengan memperhatikan tabel dalam gambar di atas, maka kita tidak bisa menggunakan rumus VLOOKUP biasa untuk mencari nama siswa.

Mengapa?

Karena kolom NOMOR INDUK yang merupakan kolom kunci terletak disebelah kanan kolom yang ingin diperoleh hasilnya yaitu kolom NAMA.

Untungnya ada fungsi CHOOSE yang bisa mengutak-atik  dan merubah arah nomor kolom. Fungsi ini bisa menentukan mana kolom ke-1, mana kolom ke-2, ke-3 dan seterusnya. 

Untuk contoh kasus yang kita hadapi, kita hanya perlu menentukan kolom ke-1 dan kolom ke-2 saja. Kolom ke-1 akan terbaca seakan akan sebagai kolom paling kiri.

Secara umum fungsi CHOOSE untuk keperluan ini dapat dituliskan sebagai berikut:

=CHOOSE({1,2},rangeKolom1,rangeKolom2)

• rangeKolom1 merupakan range kolom kunci. Dalam hal contoh adalah kolom NOMOR INDUK atau range B2:B8
• rangeKolom2 merupakan range kolom hasil. Sebagai contoh adalah kolom NAMA atau range A2:A8

=VLOOKUP(NilaiDicari, Tabel, NomorUrutKolom,[LingkupPencarian])

Penjelasan lebih detail mengenai fungsi VLOOKUP dapat dilihat di artikel Rumus VLOOKUP untuk Apa Sich? 

Nilai argumen untuk penerapanan fungsi pada kasus sesuai contoh:

• NilaiDicari = NOMOR INDUK ⇒ merupakan kata kunci untuk pencarian data lainnya yang terkait
• Tabel = range tabel data NAMA dan NOMOR INDUK ⇒ dikarenakan kolom kunci NOMOR INDUK ada disebelah kanan,  maka argumen tabel harus kita modifikasi menggunakan fungsi CHOOSE
• NomorUrutKolom = 2 karena kita akan jadikan kolom yang diinginkan nilainya (kolom NAMA) sebagai kolom ke-2
• LingkupPencarian = 0, bersifat opsional namun sangat penting. Kita isi saja angka nol (0) supaya pencarian bersifat matching.

Selanjutnya secara umum rumus dapat dituliskan sebagai berikut:

=VLOOKUP(NilaiDicari, Tabel, NomorUrutKolom,[LingkupPencarian])

Berikutnya parameter tabel kita ganti dengan rumus CHOOSE

=VLOOKUP(NilaiDicari, CHOOSE({1,2},rangeKolom1,rangeKolom2)

,NomorUrutKolom,[LingkupPencarian])

Dengan mengganti argumen fungsi VLOOKUP dan CHOOSE dengan nilai dan referensi sesuai tabel contoh maka rumus untuk mencari NAMA yang memiliki NOMOR INDUK yang tertulis di sel F2, dapat dituliskan sebagai berikut:

=VLOOKUP(F2,CHOOSE({1,2},B2:B8,A2:A8),2,0)

Penerapan rumus tersebut dalam lembar kerja excel dapat dilihat dalam gambar berikut:

Sampai di sini, kita sudah berhasil menjawab pertanyaan yang sering muncul di antara user excel. Bagaimana melakukan VLOOKUP jika kolom kunci ada di sebelah kanan. Ada bebera alternative rumus memang, untuk menyelesaikan contoh kasus tersebut. Rumus VLOOKUP CHOOSE hanyalah salah satunya.

Demikian pembahasan singkat mengenai Contoh Rumus Excel VLOOKUP yang dapt mencari data dari kanan ke kiri. Semoga penjelasannya dapat mudah difahami. Harapannya tulisan ini bisa berguna bagi kita semua. 

Terimakasih.

Contoh Rumus Excel Lainnya:

• Rumus VLOOKUP 2 Dimensi
• Rumus VLOOKUP 2 Kriteria
• Rumus VLOOKUP untuk Tanggal
• Rumus VLOOKUP untuk Mencari Nama Barang

Rumus VLOOKUP 2 Dimensi

Rumus VLOOKUP 2 dimensi bekerja secara dua arah, baik vertikal maupun horizontal. Rumus ini sangat berguna untuk mencari data yang berubah secara periodik. Misalnya data nilai penjualan yang berubah setiap bulannya. Dengan rumus VLOOKUP dua dimensi maka kita bisa mencari data nilai penjualan pada bulan tertentu secara mudah dan cepat. Untuk tugas tersebut, fungsi VLOOKUP tidak bisa bekerja sendiri, namun harus dibantu oleh fungsi lainnya, yakni fungsi MATCH.

Contoh soal:

Tersedia sebuah tabel berisi data penjualan produk buah-buahan selama beberapa bulan. Nama produk buah-buahan disusun dalam dimensi vertikal. Sedangkan data per bulannya disusun dalam dimensi horizontal. Untuk lebih jelas mengenai gambaran soalnya, silahkan diperhatikan gambar di bawah ini.

Tugas kita selanjutnya adalah merumuskan formula yang tepat untuk mencari nilai penjualan produk buah tertentu pada bulan tertentu pula.

Misalnya: Berapa nilai penjualan buan Apel pada Bulan Februari?

Secara kasat mata, dengan hanya menampilkan data tabel di atas, sebenarnya kita bisa langsung mengetahui bahwa penjualan buah apel pada bulan Februari adalah 500. 

Bagaimana kita bisa langsung mengatakan bahwa penjualan buah Apel pada bulan Februari adalah senilai 500?

Mudah saja, karena mata kita akan melihat garis temu antara garis apel secara horizontal dan kolom Februari secara vertikal, kemudian otak kita menyimpulkan bahwa titik temu tersebut adalah sebuah sel dengan konten nilai 500. Analogi inilah yang kita gunakan dalam logika rumus excel VLOOKUP 2 Dimensi untuk mencari titik temu antara baris kriteria1 dan kolom kriteria2.

Contoh Rumus VLOOKUP 2 Dimensi Untuk Mencari Nilai Penjualan Sebuah Produk Pada Bulan Tertentu.

Secara umum rumus untuk tujuan dimaksud dapat dituliskan sebagai berikut:

=VLOOKUP(produk,rangeTabel,MATCH(bulan,rangeHeader,0),0)

• Produk = nama produk yang akan dicari nilai penjualannya pada bulan tertentu (Contoh : buah "Apel")
• rangeTabel = referensi range tabel data, boleh tanpa header (contoh = range A2:D4), namun bisa juga termasuk header (range A1:D4)
• bulan = nama bulan (Contoh = "Februari")
• rangeHeader = referensi header kolom tabel. (Contoh = range A1:D1)

Sehingga untuk mencari nilai penjualan buah “Apel” pada bulan “Februari”, rumusnya dapat dituliskan sebagai berikut:

=VLOOKUP("apel",rangeTabel,MATCH("Februari",rangeHeader,0),0)

atau

=VLOOKUP("apel",A2:D4,MATCH("Februari",A1:D1,0),0)

Dan hasilnya = 500

Cara kerja rumus VLOOKUP 2 dimensional untuk mencari nilai penjualan sebuah produk pada bulan tertentu.

Untuk memahami rumus di atas, mari kita bedah dan pecah rumus menjadi fungsi VLOOKUP dan Fungsi MATCH.

• Fungsi VLOOKUP bekerja pada dimensi Vertikal.
• Fungsi MATCH bekerja pada dimensi horizontal (untuk kasus dalam contoh, secara aplikatif fungsi MATCH juga bisa bekerja secara vertikal)

Sebagaimana kita ketahui bahwa fungsi VLOOKUP berguna untuk mencari data pada kolom pertama sebuah tabel dan mendapatkan nilai dari nomor urut kolom tertentu yang sebaris dengan data ditemukan di kolom pertama.

Misalnya:

=VLOOKUP("apel",A2:D4,nomorKolom,0)

Rumus tersebut akan mencari "apel" pada kolom pertama dari range A2:D4

Setelah "apel" ditemukan di kolom pertama, kemudian VLOOKUP akan menoleh ke sebelah kanan menuju nomorKolom tertentu yang ditentukan, dan mendapatkan nilainya.

Selandainya kita ganti argumen nomorKolom menjadi 1, 2, 3, dan 4 maka rumusnya akan seperti ini.

=VLOOKUP("apel",A2:D4,1,0) ⇒ hasil = "Apel" 

=VLOOKUP("apel",A2:D4,2,0) ⇒ hasil = 850

=VLOOKUP("apel",A2:D4,3,0) ⇒ hasil = 500

=VLOOKUP("apel",A2:D4,4,0) ⇒ hasil = 750

Dapat kita lihat hasil dari rumus - rumus di atas. Ternyata hasil dari rumus pertama sampai ke-4 bergerak dari kiri ke kanan pada baris apel sesuai nomor urut kolomnya.

Kembali ke pertanyaan awal: Bagaimana mencari nilai penjualan produk Apel di bulan Februari. Kita ketahui bulan Februari merupakan kolom ke-3. Nah bagaimana supaya rumus bisa mengenali bahwa bulan Februari itu merupakan kolom ke-3? Disinilah peranan fungsi MATCH.

Mari kita perhatikan kembali rumus berikut ini

=MATCH("Februari",rangeHeader,0)

Rumus tersebut berguna untuk mencari nomor urut kolom "Februari" pada rangeHeader. Jika range headernya kita ganti dengan range A1:D1 maka rumusnya menjadi 

=MATCH("Februari",A1:D1,0)

Dan hasilnya adalah 3, karena memang ("Februari") terletak pada kolom ke-3 dalam range A1:D1. 

Kemampuan fungsi MATCH ini untuk mendapatkan nomor urut kolom tertentu pada sebuah tabel maka fungsi ini dapat bekerjasama dengan fungsi VLOOKUP dalam rumus pencarian data 2 dimensi. 

Fungsi MATCH menyediakan informasi nomorKolom yang dapat digunakan selanjutnya oleh fungsi VLOOKUP. 

=VLOOKUP("apel",rangeTabel,nomorKolom,0)

Menjadi:

=VLOOKUP("apel",rangeTabel,MATCH("Februari",rangeHeader,0),0)

Bila argument-argumen rumus tersebut kita ganti dengan referensi sesuai contoh soal.

• Nama buah = sel D7
• Nama Bulan = Sel D8
• rangeTabel = A2:D4, atau A1:D4
• rangeHeader = A1:D1

Maka rumusnya (di sel D9) menjadi seperti berikut:

=VLOOKUP(D7,A2:D4,MATCH(D8,A1:D1,0),0)

Berikut screenshot penampakannya dalam spreadsheet:

Silahkan dicoba langsung dalam lembar kerja excel dan perhatikan hasilnya. Kemudian coba rubah nama buah (sel D7) dan nama bulan (sel D8) dan perhatikan lagi hasilnya. Kita akan melihat hasil rumus VLOOKUP 2 dimensi berubah mengikuti nilai penjualan produk buah pada bulan yang ditentukan.

Demikian pembahasan singkat mengenai contoh rumus VLOOKUP 2 Dimensi untuk mencari nilai penjualan sebuah produk pada bulan tertentu. Semoga penjelasannya mudah difahami.

Terimakasih.

Contoh Rumus Excel Lainnya:

• Rumus VLOOKUP untuk Apa Sich??
• Rumus VLOOKUP 2 Kriteria
• Rumus VLOOKUP untuk Mencari Nama Barang
• Rumus VLOOKUP untuk Tanggal

Rumus VLOOKUP 2 Kriteria

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

Baik, selanjutnya mari kita bahas dengan contoh soal rumus excel:

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

Rumus VLOOKUP Untuk Apa Sich?

Rumus VLOOKUP untuk apa ya? Pertanyaan ini kerap muncul dalam sebuah presentasi kecil-kecilan yang menampilkan data excel. Ironis, meskipun fungsi ini sudah sangat populer di kalangan pengguna excel, namun kenyataannya masih saja ada user yang belum mengerti dan memanfaatkan fungsi VLOOKUP. Padahal, tidak sedikit diantaranya sudah sangat lama berkecimpung mengoperasikan excel.

Mungkin pembaca belum memanfaatkan fungsi VLOOKUP karena tidak tahu untuk apa dan bagaimana cara menggunakannya. Mudah-mudahan penjelasan singkat berikut ini dapat membantu memahami bagaimana pentingnya fungsi VLOOKUP dan bagaimana cara menggunakannya untuk apa saja dalam tugas-tugas terkait pencarian data.

Sebelum melanjutkan pembahasan, video sederhana berikug akan membuat anda lebih faham mengenai apa fungsi dan cara menggunakan fungsi VLOOKUP. Simak baik-baik ya...

VLOOKUP seperti yang tersirat dari namanya : Vertical Lookup yaitu fungsi yang berguna untuk mencari data apa saja secara vertikal dari atas ke bawah dalam sebuah kolom tertentu. Kemudian jika data yang dicari tersebut sudah ketemu, maka Vlookup akan memberikan informasi dengan mengambil data dari kolom lainnya sesuai nomor urut kolom tertentu yang ditentukan dalam rumus.

Adapun syntax fungsi VLOOKUP adalah sebagai berikut:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Jika di-Indonesia-kan, bahasa VLOOKUP tersebut dapat ditulis kira-kira seperti ini.

VLOOKUP(NilaiDicari, Tabel, NomorUrutKolom,[LingkupPencarian])

Atau secara singkat dapat dijabarkan bahwa VLOOKUP mencari NilaiDicari pada Kolom pertama dari Tabel dan mengembalikan nilai dari NomorUrutKolom yang sebaris dengan posisi nilaiDicari yang sudah ditemukan dalam Tabel. Lingkup pencarian menentukan apakah mode true (pendekatan nilai) atau false (bukan pendekatan nilai, nilai harus matching).

Contoh Kasus.

Contoh yang sederhana, namun penting dan sering dijumpai dalam pekerjaan adalah bagaimana menggali keterangan data karyawan berdasarkan NIK yang sudah diketahui. Kita tinggal memasukan NIK, maka otomatis nama dan data lainnya yang terkait karyawan tersebut akan terdisplay.

Dalam hal ini kita memiliki tabel data karyawan terdiri atas 5 kolom dengan urutan kolom sebagai berikut:

Kolom ke-1 = berisi data NIK

Kolom ke-2 = berisi data Nama

Kolom ke-3 = berisi data Jenis Kelamin

Kolom ke-4 = berisi data Tanggal Lahir

Kolom ke-5 = berisi data Asal Kota

Kemudian satu-satunya informasi yang sudah kita miliki adalah nomor NIK karyawan. Nah, berbekal kata kunci tersebut, selanjutnya bagaimana kita dapat menggali informasi lainnya? Disinilah rumus VLOOKUP memainkan perannya.

Perhatikan ilustrasi dalam screenshot di bawah ini.

Berdasarkan gambar diatas, kita hanya disediakan satu kotak isian yaitu data NIK yang harus diketika manual, sedangkan data-data lainnya akan muncul secara otomatis setelah kita menginput NIK.

Perhatikan rumus pada baris ke-dua yang dapat menghasilkan data nama “Ratna”. Kenapa excel bisa tahu bahwa yang memiliki NIK 12562 adalah si Ratna. 

Adapun rumusnya adalah sebagai berikut.

=VLOOKUP(C$1,$A$12:$E$16,2,0)

Kemudian coba bandingkan dengan syntax fungsi VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Mari kita jelaskan masing – masing komponen fungsi.

• Lookup_value  yaitu nilai yang dicari. Dalam hal ini adalah konten sel C1 yaitu NIK Karyawan 12562
• Table_array, yaitu tabel dimana dilakukan pencarian dan pengambilan data. Dalam contoh diatas tabel tersebut berada pada range A12:E16.  Perhatikan : kolom pertama tabel tersebut harus berisi nata NIK Karyawan yaitu kolom kunci untuk proses pencarian.
• Col_index_num yaitu nomor urut kolom dalam tabel dimana nantinya rumus VLOOKUP akan mengambil data yang diperlukan. Contoh angka 2 berarti untuk mengambil data pada kolom ke-2, yaitu data nama.
• Range_lookup bersifat opsional, namun sangat penting, yaitu mode lingkup pencarian. Angka 0 menunjukan pencarian data bersifat matching, dan tidak dipengaruhi oleh urutan data apakah bersifat berurutan atau acak. Kita gunakan angka 0 karena kita tahu kita harus mencari NIK yang tepat sama dengan NIK kunci, dan uturan NIK pada tabel pun tidak berurutan (ascending atau descending)
• Kesimpulan: Rumus VLOOKUP mencari NIK 12562 (konten C1) pada tabel pertama range A12:E16, dan memberikan informasi dari kolom ke-2 yang sejajar dengan posisi NIK yang dicari. Hasilnya : Ratna.

Berikutnya kita hanya tinggal merubah parameter col_index_num atau nomor urut kolom untuk mendapatkan informasi lainnya yaitu jenis kelamin (kolom ke-3), tanggal lahir (kolom ke-4) dan asal kota (kolom ke-5).

Sampai disini pembahasan singkat untuk menjawab pertanyaan: Rumus VLOOKUP Untuk Apa? Mudah-mudahan contoh dan penjelasannya mudah difahami sehingga bisa diterapkan dalam pekerjaan, khususnya jika kita sering berkecimpung dengan database excel yang besar dimanaVLOOKUP akan sangat berguna dalam tugas pencarian data.

Terimakasih.

Artikel terkait

Rumus VLOOKUP Untuk Tanggal

Rumus VLOOKUP Untuk Mencari Nama Barang

Rumus VLOOKUP Untuk Pengelompokan Nilai

Rumus VLOOKUP Untuk Tanggal

Penggunaan rumus VLOOKUP untuk tanggal penting ketika kita perlu mencari data terkait aktifitas pada tanggal tertentu, misalnya status absensi karyawan pada tanggal tertentu, agenda penting pada tanggal tertentu, pengaturan tim kerja pada range tanggal tertentu, dan lain sebagainya. 

Bagaimana cara menggunakan rumus VLOOKUP untuk tanggal? 

Yuk kita perhatikan dan ikuti baik-baik contoh sederhana penggunaan fungsi VLOOKUP ini untuk mencari data berdasarkan tanggal yang sudah diketahui.

Contoh 1: Rumus VLOOKUP untuk Tanggal Tunggal - mengetahui status kehadiran karyawan pada tanggal tertentu.

Untuk membuat rumus ini, langkah pertama yang harus kita persiapkan adalah list data absensi karyawan. List tersebut berisi kolom tanggal tunggal (setiap hari).

Contoh paling sederhana nya kita ambil list status absensi untuk satu orang karyawan saja, anggaplah namanya Jono. List tanggal dan status absensi kita taroh di kolom A dan B. Kemudian tanggal yang dicari kita taruh di sel lainnya, anggaplah di sel D3.

Maka rumus VLOOKUP untuk mencari status absensi si Jono adalah sebagai berikut:

=VLOOKUP(D2,A3:B11,2)

Adapun kenampakannya dalam spread sheet mungkin akan seperti dalam gambar berikut:

Untuk membuat rumus VLOOKUP tanggal tanpa menggunakan referensi sel yang berfungsi menyimpan tanggal yang dicari maka kita dapat mengetikan tanggal secara langsung pada rumus VLOOKUP. Dalam hal ini kita perlu menggunakan bantuan fungsi DATEVALUE  untuk mengkonversi text tanggal menjadi nilai tanggal.

Misalnya untuk mencari status kehadiran karyawan pada tanggal 10-04-2018, rumusnya dapat dituliskan sebagai berikut:

=VLOOKUP(DATEVALUE("04-06-2018"),A2:B11,2,0)

Formula di atas jika menggnakan list data absensi si Jono maka akan menghasilkan nilai mangkir.

Perhatikan format penulisan tanggal sebagai parameter fungsi DATEVALUE

("04-06-2018" dibaca sebagai tanggal 6 bulan 4 tahun 2018,  bukan tanggal 4 bulan 6 tahun 2018. Hal tersebut tergantung regional setting pada komputer  yang kita gunakan.

Contoh 2: Rumus VLOOKUP Untuk Mengetahui Team Kerja Yang Bertugas Pada Range Tanggal Tertentu

Misalnya dalam sebuah organisasi kerja, sudah diatur jadwal team yang bertugas dalam range tanggal yang ditentukan. Nah, kita bisa menggunakan rumus VLOOKUP  untuk mengetahui tim mana sih yang seharusnya bertugas pada tanggal tertentu.

Untuk keperluan ini, seperti biasa perlu dipersiapkan terlebih dahulu list range tanggal dan data tim mana yang bekerja pada range tanggal tersebut. Anggaplah begini:

• Tanggal 1 – 5 April  2018  = Tim A
• Tanggal 6 – 10 April 2018 = Tim B
• Tanggal 11- 15 April 2018 = Tim C
• Tanggal 16-20 April 2018 = Tim A
• Dan seterusnya…

Supaya  jadwal tugas tersebut bisa dibaca oleh fungsi VLOOKUP, maka harus dibuat seperti dalam gambar berikut:

Selanjutnya, anggaplah kita ingin membuat rumus yang secara otomatis dapat memberikan informasi mengenai tim mana yang bertugas pada tanggal tertentu.

Anggaplah tanggal yang dicari diletakan di sel E2, maka rumusnya dapat diketikan di sel E3 sebagai berikut:

=VLOOKUP(E2,A2:B5,2)

Penting diperhatikan:

• List range tanggal harus disusun berurutan dari terkecil ke yang terbesar, perhatikan kolom A.
• Rumus VLOOKUP akan mengelompokan tanggal yang dicari ke tanggal yang sama dari tanggal yang dicari, atau tanggal yang lebih kecil terbesar dari tanggal yang dicari.
• Dalam kasus nomor 2 ini sebenarnya cukup diperlukan 3 parameter rumus vlookup saja. Tetapi jika mau, kita juga bisa menambahkan angka 1 atau TRUE sebagai parameter ke-4. Sehingga rumus terakhir dapat juga dituliskan  =VLOOKUP(E2,A2:B5,2,1)  atau  =VLOOKUP(E2,A2:B5,2,TRUE)

  

Demikian pembahasan ringkas dan sederhana mengenai rumus VLOOKUP untuk tanggal. Semoga bermanfaat.

Terimakasih.

Artikel terkait:

Rumus VLOOKUP Untuk Mencari Nama Barang

Rumus VLOOKUP Untuk Pengelompokan Nilai
Rumus VLOOKUP Untuk Apa Sich?
Menghitung Data Antara 2 Tanggal