Belajar Microsoft Excel: Mengerti Rumus Vlookup Dengan Studi Kasus

Blog / Oleh

Memahami Rumus VLOOKUPCara gampang mengetahui fungsi dan rumus VLOOKUP di excel merupakan dengan  cara mempraktekannya secara eksklusif untuk memecahkan permasalahan 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 pecahan ini , diperlukan anda sudah mengetahui dasar-dasar penggunaan fungsi VLOOKUP apalagi dahulu.

Jika anda masih gres menggunakan fungsi ini dan belum mengetahui bagaimana cara kerjanya , silahkan untuk membaca apalagi dahulu postingan panduan pecahan pertama : VLOOKUP – Rumus Jitu Yang Harus Dikuasai Pengguna Excel

Secara garis besar , bahasan mencar ilmu excel kali ini adalah:
  • Rumus VLOOKUP Excel dengan dua tolok ukur atau criteria ganda
  • Rumus VLOOKUP untuk Mendapatkan eksistensi nilai pertama , kedua , ketiga dan Seterusnya
  • VLOOKUP dua dimensi
Kita sudah mengenal kehebatan rumus VLOOKUP untuk mencari nilai tertentu dalam suatu database dan mengexplorasi data yang lain yang terkait dengan nilai tersebut.
Namun sebagaimana kita pahami , Fungsi ini cuma membolehkan satu tolok ukur nilai yang dicari.

Sedangkan dalam prakteknya , kita sering mendapatkan permasalahan dimana ada beberapa keadaan dengan banyak sekali criteria yang mesti dilihat dan diobservasi lebih lanjut.

Contoh VLOOKUP dengan 2 Criteria

Misalnya : Di suatu perkebunan , kita ingin mengenali berapa sih hasil panen dari masing – masing blok panen per tenaga kerja pemanen? .
Dengan kata lain , kita memiliki 2 tolok ukur yang mesti diamati untuk mencari nilai angka hasil panen.

Criteria yang dimaksud adalah:

  1. Nama Blok
  2. Nama Pemanen
Perhatikan gambaran berikut:
Contoh Studi Kasus Rumus VLOOKUP Kriteria Ganda
Kita sudah mengenali bahwa VLOOKUP cuma sanggup membaca satu kolom pertama untuk penelusuran nilai (sebuah kriteria) dan mendapat nilai dari kolom yang lain dari suatu tabel.
Sementara dalam pola permasalahan ini , kita memiliki 2 kriteria.
Lantas bagaimana solusinya?
Solusinya merupakan menyebabkan 2 tolok ukur menjadi 1 tolok ukur dengan cara menggabungkannya.

Dalam pola permasalahan ini kita perlu memadukan nama blok dan nama pemanen di dalam suatu kolom bantu.

Dalam gambaran di atas , saya sudah menyiapkan suatu kolom kosong (kolom A) untuk dijadikan selaku kolom bantu.
  • Pada sel A2 , Ketikan rumus berikut=C2&D2
  • Kemudian copy rumus dari sel A2 ke baris selanjutnya 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:
Solusi Untuk Kasus VLOOKUP Excel Kriteria Ganda
Referensi Keseluruhan Data Dalam Kolom (Entire Column)
Perhatikan kembali rumus =VLOOKUP(H2&H3 ,A:E ,5 ,0)
Untuk parameter table_array , saya menggunakan rujukan A:E.
Lho.. ko kenapa cuma kolom , tidak ada barisnya?.
Memang sengaja , sebab pada prakteknya , penggunaan fungsi VLOOKUP sering melibatkan data yang sungguh banyak , ribuan , puluhan ribu , bahkan hingga batas kesanggupan jumlah baris yang ditawarkan 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).

Baca Juga  Belajar Microsoft Excel: Menangani Error "Too Many Different Cell Formats"

Ini penting mudah-mudahan jikalau ada penambahan data pada baris selanjutnya , kita tidak perlu mengganti rujukan dalam rumus VLOOKUP.

Ada sih , cara lain untuk rujukan tabel , yakni dengan menggunakan data tabel (excel 2007 ke atas) atau data list (excel 2003). Tapi bukan disini pembahasannya mudah-mudahan tidak rancu dengan ungkapan data table. Dan lupakan saja paragraf ini mudah-mudahan tidak membingungkan … 🙂

VLOOKUP 2 Kriteria dari Sheet yang Berbeda


Jika anda sudah sudah biasa dengan excel. Kemungkinan besar anda mesti melakukan pekerjaan dengan beberapa sheet. Termasuk dalam menggunakan rumus VLOOKUP.
Jadi bagaimana caranya menjalankan VLOOKUP tolok ukur ganda antar sheet.
Sebenarnya caranya sama saja. Hanya saja rujukan 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 tolok ukur atau Lebih

Dengan cara yang serupa kita juga bisa menciptakan VLOOKUP dengan lebih dari dua kriteria.
Trik nya simple saja
  • Buat kolom bantu pada pecahan paling kiri tabel
  • Isi kolom bantu tersebut dengan rumus untuk memadukan tolok ukur yang masih terpisah di kolom lainnya. Bisa dengan rumus menggunakan operator & (ampersand) ataupun menggunakan fungsi CONCATENATE
  • Buat rumus VLOOKUP dimana parameter lookup_value merupakan penggabungan beberapa tolok ukur yang serupa dengan penggabungan pada kolom bantu.
  • Jika belum faham , silahkan baca kembali ke pecahan atas yakni VLOOKUP dengan dua criteria. Karena berapapun tolok ukur yang digunakan , caranya sama… 🙂 cuma tinggal penyesuaian rumus pada kolom bantu dan rumus VLOOKUP –nya.
Sekali lagi : Perlu dikenang seputar penggunaan VLOOKUP dengan criteria ganda
  • Penggunaan rumus VLOOKUP untuk mencari dengan tolok ukur ganda mesti melibatkan suatu kolom bantu pada tabel_array
  • Kolom bantu mesti terletak pada pecahan kiri dari suatu tabel , kecuali jikalau menggunakan rumus adonan VLOOKUP dan CHOOSE dimana kita bisa meletakan kolom bantu di sembarang posisi. Silahkan baca kembali postingan VLOOKUP pecahan pertama jikalau anda belum mengetahui dasar-dasar VLOOKUP dari kanan ke kiri.
  • Kolom bantu mesti berisi nilai unik. Nilai unik ini berupa adonan text tolok ukur yang ditetapkan
  • Untuk memadukan criteria sanggup menggunakan fungsi CONCATENATE atau operator &. Silahkan diseleksi mana yang lebih gampang dan tenteram bagi anda. Kalau saya sendiri lazimnya menggunakan 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 menyertakan text selanjutnya sesuai kebutuhan.
  • Misalnya: jikalau kita menginginkan data dengan tanggal , blok dan pemanen (3 kriteria) maka kita mesti menciptakan rumus yang sanggup diilustrasikan selaku 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 eksistensi nilai pertama , kedua , ketiga dan Seterusnya

Sebagaimana kita pahami bahwa fungsi VLOOKUP akan menciptakan nilai dari tolok ukur yang posisinya ditemui paling atas dalam suatu tabel. Sehingga jikalau ada criteria atau nama yang serupa dalam table , maka kita tidak dapat menginginkan nilai yang lain selain yang ditemui pertama pada table.
Namun dengan sedikit penyesuaian pada kolom bantu , kita sanggup mendapatkan nilai hasil panen pekerja panen yang serupa sesuai nomor urut kehadiran nama tersebut dalam tabel.
Bagaimana caranya? Gunakan rumus COUNTIF pada kolom bantu untuk menyertakan nomor urut kehadiran pada nama pemanen.
Menggunakan tabel yang serupa menyerupai pola sebelumnya , Ketikan rumus pada sel A2 =D2&COUNTIF($D$2:D2;D2) , lalu copy rumus tersebut ke baris selanjutnya pada kolom bantu (A)
Kemudian penyesuaian label dan data tolok ukur yang diketahui
  • Sel G2 = Nama Pemanen
  • Sel G3 = Nomor urut
  • Sel G4 = Hasil Panen
  • Sel H2 = Isi dengan Nama pemanen yang hendak dicari hasil panennya
  • Sel H3 = Isi dengan Nomor urut kehadiran nama pemanen dalam tabel
  • Sel H4 = Tuliskan rumus =VLOOKUP(H2&H3 ,A:E ,5 ,0)
Untuk lebih jelasanya , amati gambaran berikut:
Contoh Studi Kasus VLOOKUP Excel Nomor Urut

VLOOKUP Dua Dimensi

Baca Juga  Belajar Microsoft Excel: Control Conditional Formating Dengan Check Box
Melakukan lookup 2 dimensi artinya merupakan mencari nilai tertentu dengan didasarkan pada tolok ukur 2 arah yang terletak pada kolom dan baris. Kata yang lain merupakan mencari nilai yang merupakan konferensi antara baris dan kolom tertentu.
Mari kita telaah lebih jauh penerapan VLOOKUP 2 dimensi ini dalam permasalahan handling data excel.
Misalnya kita memiliki 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.
Contoh Studi Kasus VLOOKUP Excel Dua Arah
Sebelum menjalankan penelusuran data menggunakan rumus , mari coba laksanakan secara manual , dengan mata kepala.
  • Nama pemanen dan bulan sudah dimengerti yakni 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 dimengerti bahwa hasil panen si Ogah pada bulan April merupakan 13.500
  • Bagaimana cara kerja otak kita untuk mendapat angka tersebut?
  • Caranya merupakan menyaksikan 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 dimengerti terletak pada sel E10 dan E11
  • Tabel terletak pada range B2:N8
  • Nama pemanen terletak pada pecahan tabel pecahan kiri , ini cocok untuk penggunaan rumus VLOOKUP
  • Secara lazim , rumus untuk mendapat angka hasil panen si Ogah adalah
    • =VLOOKUP(E10 ,B2:N8 ,nomor_kolom ,0)
  • Perhatikan parameter nomor_kolom. Ini membuktikan nomor urut kolom dalam tabel.
  • Pertanyaannya: Bagaimana caranya untuk mendapat nomur urut tabel yang tepat dengan nama bulan yang ditentukan.
  • Ini sanggup dituntaskan menggunakan rumus MATCH
  • Fungsi MATCH digunakan untuk mencari nomor urut nilai atau text tertentu baik dalam baris maupun kolom
  • Dalam pola permasalahan ini digunakan untuk mencari nomor urut bulan tertentu dalam struktur kolom
  • Secara lazim , rumus untuk mendapat nomor urut menggunakan fungsi MATCH adalah 
    • =MATCH(text ,range ,lingkup)
  • Text dalam pola permasalahan ini merupakan nama bulan yang terletak dalam sel E11. Range merupakan lokasi dalam struktur baris maupun kolom dimana salah satu sel dalam range tersebut berisi text yang dicari , range dimaksud dalam pola merupakan B2:N2. Lingkup tidak usah dipertanyakan di sekarang ini , Isi saja 0 atau FALSE.
  • Jadi rumus untuk mencari nomor urut bulan sanggup dituliskan selaku berikut
    • =MATCH(E11 ,B2:N2 ,0)
  • Rumus tersebut sanggup digabung dengan rumus VLOOKUP mengambil alih parameter nomor_kolom , sehingga rumusnya menjadi: 
    • =VLOOKUP(E10 ,B2:N8 ,MATCH(E11 ,B2:N2 ,0) ,0)
Contoh Solusi Studi Kasus VLOOKUP Excel Dua Arah
Sampai pada tahap ini , kita sudah sukses menjalankan VLOOKUP 2 dimensi.

Alternative lain Lookup 2 Dimensi

Selain menggunakan rumus VLOOKUP MATCH , juga ada beberapa rumus yang sanggup dijadikan selaku alternative untuk menjalankan Lookup 2 dimensi.

Baca Juga  SHORTCUT PADA MICROSOFT OFFICE

Berikut merupakan contoh-contohnya , tetapi cuma untuk selaku citra saja bahwa “Banyak Jalan Menuju Roma”. Untuk detailnya insya Alloh akan dibahas dalam potensi 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 menggunakan nama range dan operator spasi (operator intersepsi)

Untuk alternative cara ini saya ambil heading khusus sebab ini wawasan gres bagi saya. Terus terang belum terfikir sebelumnya tentang adanya operator intersepsi. Dan cara ini ternyata sungguh 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 menggunakan excel 2007
Create From Selection Excel 2007 ,2010 ,2013 ,2016
Bagi yang masih menggunakan excel 2003. Perintah Create name ini bisa diakses dari menu Insert–> Name–>Create
Create Name From Selection Excel 2003
Setelah timbul pembicaraan box create name , pilih Top Row dan Left Column –> lalu tekan OK
Create Name From Selection Top Row and Left Colulmn
Maka excel akan meng-create nama range menurut label kolom dan baris. Bisa dicek hasilnya pada name box yang terletak pada pecahan 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 menggunakan fungsi INDIRECT , misalnya dikarenakan nama pemanen dan nama bulan yang dicari berada di sel E10 dan E11 , maka rumus sanggup dituliskan selaku 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 berkhasiat yang serupa jikalau digunakan diatara 2 fungsi INDIRECT.

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

Tapi tak apalah yang penting kita sanggup lebih mengetahui banyak sekali alternative rumus untuk tujuan yang sama. Pada akibatnya terserah anda mau menggunakan cara yang mana.

Related Posts

Need Help? Chat with us