Artikel ini akan membahas mengenai rumus VLOOKUP terlebih dahulu.
Rumus VlOOKUP adalah =vlookup[kolom acuan,tabel acuan,kolom,0]. Pada beberapa versi Microsoft Excel, pemisah tanda koma diganti dengan tanda titik koma [;].
Pada contoh di bawah, akan dimasukkan beberapa data berdasarkan tabel acuan yang ada.
Foto ini mungkin mengganggumu, apakah tetap ingin melihat?
Lihat
Untuk penulisan Fungsi VLOOKUP, Formula bakunya adalah:
=VLOOKUP[lookup_value,table_array, col_index_num,[range_lookup]]
Atau bisa juga dibaca seperti ini:
=VLOOKUP [nilai_kunci, tabel_data, nomor_kolom,tipe_data]
Penjelasan dari fungsi VLOOKUP diatas
- Nilai_kunci: merupakan nilai yang diajukan untuk membaca tabel referensi. Nilai kunci harus ada pada tabel yang akan disi maupun pada tabel referensi.
- Tabel_data: merupkan range data yang disusun tegak atau vertikal yang berfungsi sebagai tabel bantu/referensi yang akan dibaca. range pada tabel data berisikan data-data yang digunakan untuk mengisi hasil yang diharapkan. Saat anda mengambil tabel_data, pastkan anda tidak menyertakan judul kolomnya.
- Nomor_kolom: merupakan nomor urut kolom untuk pembacaan tabel referensi, dimulai dari kolom paling kiri. Dimulai dengan index ke satu atau nomor 1 , dan seterusnya.
- Tipe data: untuk tipe data ada 2 Jenis, yaitu: True digunakan jika nilai datanya tidak pasti atau berada range tertentu dan tipe data False digunakan jika nilai datanya itu pasti.
Tipe data TRUE, bisa juga digantikan dengan angka 1, Sedangkan angka 0 untuk tipe data FALSE. Bila penulisan tipe_data ini tidak anda gunakan/hilangkan. Maka otomatis fungsi Vlookup yang anda gunakan mengacu pada tipe Data True.
Saran.. Gunakan tipe data, dalam penulisan formula VLOOKUP.
Oke, setelah pemahaman diatas sudah anda pahami, Lanjut ke pengerjaan soal excel menggunakan FUNGSI VLOOKUP. Untuk memudahkan pengerjaan, silahkan ketikkan soalnya seperti gambar dibawah ini:
Pada contoh soal gambar di atas, ada 3 buah tabel yang bisa anda lihat, dimana tabel bagian operator merupakan tempat dimana anda harus menyelesaikan atau mengisi soal. Lalu ada tabel referensi untuk membantu anda menyelesaikan soal tersebut, yaitu tabel gaji dan tabel potongan pajak.
Pengertian VLOOKUP dan HLOOKUP
Pengertian VLOOKUP adalah rumus excel yang digunakan untuk mencari data dikolom pertama sebuah tabel kemudian mengembalikan nilai dikolom kanannya yang telah ditentukan posisinya.
Pengertian HLOOKUP adalah rumus excel yang digunakan untuk mencari data dibaris pertama sebuah tabel kemudian mengembalikan nilai dibaris bawahnya yang telah ditentukan posisinya
Dari kedua pengertian VLOOKUP dan HLOOKUP diatas sudah bisa ditemukan apa sih perbedaan antara VLOOKUP dan HLOOKUP.
Keduanya sama-sama fungsi untuk mencari data [lookup], mempunyai cara kerja yang sama dalam mencari data. Perbedaannya adalah lokasi data yang dicari dan arah pencariannya.
Untuk memperjelas berikut adalah contoh yang sama dan bagaimana VLOOKUP dan HLOOKUP menyelesaikannya.
Cara Menggunakan VLOOKUP dan HLOOKUP
Misal ada data seperti gambar dibawah. Berapakah harga jus apel?
Fungsi manakah yang harus dipakai? VLOOKUP atau HLOOKUP?. Nama jus ada dikolom paling kiri sebuah tabel dengan harga ada disebelah kanannya, artinya fungsi yang harus digunakan adalah VLOOKUP.
- Letakkan kursor dicell E3
- Ketik formula untuk rumus VLOOKUP
- Parameter lookup_value diisi dengan cell D2, cell yang berisi nama jus yang akan dicari harganya
- Parameter table_array diisi dengan tabel harga jus yang berada dirange A2:B9
- Parameter col_index_num diisi dengan angka 2, yaitu urutan nomor kolom ditable_array yang berisi harga jus
- Parameter range_lookup diisi dengan FALSE, karena akan mencari nama jus yang benar-benar sama
Jika formula sudah diketik dengan lengkap akan menjadi seperti rumus dibawah
=VLOOKUP[D2,A2:B9,2,FALSE]
Jika sudah tekan tombol ENTER. Hasilnya adalah 9.900
Bagaimana fungsi VLOOKUP bisa menghasilkan angka 9.900?, berikut adalah cara kerjanya.
Langkah pertama fungsi VLOOKUP akan mencari data yang ada didalam lookup_value dikolom pertama table_array. Contoh diatas data yang dicari adalah “Jus Apel”.
Proses pencarian dimulai dari baris pertama, jika data tidak ditemukan berlanjut ke baris kedua dan seterusnya, sampai fungsi VLOOKUP menemukan data yang benar-benar sama dengan data diparameter lookup_value.
Proses pencarian dari baris pertama, ke baris kedua dan seterusnya akan membentuk garis vertical, dari sinilah nama VLOOKUP berasal. Huruf V didepan kata VLOOKUP adalah singkatan dari kata vertical. Jadi VLOOKUP adalah singkatan dari Vertical Lookup.
Langkah kedua setelah data lookup_value ditemukan maka fungsi VLOOKUP akan mencari data dikolom berapakah yang harus diambil. Nomor kolom ditentukan oleh parameter col_index_name.
Dari contoh diatas data yang diambil adalah kolom kedua yaitu kolom harga. Hasilnya adalah angka 9.900. Jika parameter col_index_num diisi dengan angka 1 maka hasilnya akan sama dengan data lookup_value.
****
Dengan menggunakan data yang sama dengan contoh kasus diatas tetapi tabel harga jus diubah posisinya seperti gambar dibawah. Berapakah harga jus apel?
Fungsi manakah yang harus digunakan?, karena nama jus ada dibaris pertama sebuah tabel harga dan harga jus ada dibaris bawahnya maka fungsi yang digunakan adalah fungsi HLOOKUP.
- Letakkan kursor dicell B5
- Ketik formula untuk rumus HLOOKUP
- Parameter lookup_value diisi dengan cell A5, cell yang berisi nama jus yang akan dicari harganya
- Parameter table_array diisi dengan tabel harga jus yang berada dirange B1:I2
- Parameter row_index_num diisi dengan angka 2, yaitu urutan nomor baris ditable_array yang berisi harga jus
- Parameter range_lookup diisi dengan FALSE, karena akan mencari nama jus yang benar-benar sama
Jika formula sudah diketik dengan lengkap akan menjadi seperti rumus dibawah
=HLOOKUP[A5,$B$1:$I$2,2,FALSE]
Jika sudah tekan tombol ENTER. Hasilnya adalah 9.900
Bagaimanakah cara kerja fungsi HLOOKUP sampai menghasilkan angka 9.900.
Langkah pertama fungsi HLOOKUP akan mencari data yang ada diparameter lookup_value yaitu Jus Apel. HLOOKUP akan mencari data dibaris pertama table_array yaitu baris 1 mulai dari kolom paling kiri [kolom B] sampai kolom terakhir [kolom I].
Jika data tidak ditemukan dikolom A maka proses pencarian bergeser ke kolom berikutnya sampai data ditemukan. Proses pencarian dari kolom B sampai kolom I akan membentuk garis horizontal, dari sinilah nama HLOOKUP berasal. Huruf H didepan nama HLOOKUP singkatan dari horizontal. Jadi HLOOKUP adalah singkatan dari Horizontal Lookup.
Setelah data yang dicari ditemukan langkah selanjutnya adalah mencari baris manakah yang harus diambil datanya, nomor baris yang akan diambil datanya ditentukan oleh parameter row_index_num.
Dari rumus HLOOKUP diatas parameter row_index_num diisi dengan angka 2, artinya data yang diabil ada dibaris kedua yaitu harga jus. Jika diisi dengan angka 1 maka hasilnya akan sama dengan parameter lookup_value.
Jelaskan perbedahan antara fungsi VLOOKUP dan HLOOKUP
A. Pengertian Rumus Excel VLOOKUP & HLOOKUP
Rumus Excel VLOOKUP dan HLOOKUP adalah rumus yang paling sering digunakan membuat lookup function untuk melihat data secara vertikal dan horizontal. Berbeda dengan rumus LOOKUP yang dapat mencocokkan data teks maupun numerik, sedangkan rumus VLOOKUP dan HLOOKUP khusus untuk mencocokkan data numerik. Penggunaan rumus VLOOKUP dan HLOOKUP merupakan kompleksitas numerik terhadap rumus LOOKUP yang sederhana. Berikut cara menggunakan VLOOKUP dan HLOOKUP beserta contohnya.
Navigasi Cepat:
- Cara VLOOKUP pada Microsoft Excel
- Cara HLOOKUP pada Microsoft Excel
Apa itu Hlookup??
Fungsi HLookup, kependekan dari fungsi Pencarian Horisontal, adalah salah satu fungsi pencarian bawaan di Excel yang digunakan untuk menemukan nilai secara horizontal di seluruh rangkaian baris dalam tabel. Fungsi ini mirip dengan VLookup, kecuali itu digunakan ketika data terdaftar dalam baris, bukan kolom. Fungsi ini mencari nilai di baris atas tabel dan mengembalikan nilai yang sesuai dari baris lain tabel. Fungsi ini menggunakan empat argumen: Lookup_Value, Table_Array, Row_Index, dan Range_Lookup. Ini mengembalikan nilai dalam tabel berdasarkan pada dua argumen fungsi dan dapat mengambil string dan angka sebagai argumen. True mengacu pada perkiraan kecocokan dengan nilai pencarian, sedangkan False mengacu pada kecocokan persis.
Apa itu Vlookup??
Fungsi VLookup, atau Vertical Lookup, adalah rumus pencarian paling populer dan tertua yang digunakan di Excel untuk mencari informasi atau data tertentu dalam spreadsheet. Misalnya, Anda dapat mencari harga barang tertentu dari daftar panjang produk bersama dengan harga. Tidak seperti fungsi HLookup, ini digunakan ketika data terdaftar dalam kolom, bukan baris dan melakukan pencarian vertikal melalui kolom dalam tabel. Ini mencari nilai di kolom paling kiri tabel dan mengembalikan nilai untuk perkiraan kecocokan. Itu bisa BENAR atau SALAH.
Fungsi Vlookup dan Hlookup pada MS Excel
Vlookup dan Hlookup pada ms.excel digunakan untuk mencari data pada table sumber. Perbedaan dari Vlookup dan Hlookup terletak pada sumber dari table tersebut. Contoh kasus paling sering terjadi pada penggunaan rumus ini adalah ketika kita ingin mencari nama suatu barang atau data yang banyak dan kita sudah mempunyai kode barang tersebut kita tinggal membuat table sumbernya . perhatikan ilustrasi Penggunaan nya seperti pada table dibawah ini.
| Fungsi Vlookup dan Hlookup pada MS Excel |
Kasus Penggunaan VLookup dan Hlookup yang sering terjadi
Pada table diatasi kita akan mengisi Kolom jurusan dengan ketentuan pengisian menurut kode berikut
Kode AK = AKuntansi
Kode TK = Teknik Komputer
Kode TKIN = Teknik kima
Kode TT = Tata Boga
Nah kita tidak mungkian kan mengisi data pada kolom jurusan secara manual sehingga mau tidak mau harus mengisi atau membuat table kedua sebagia sumber referensi nah inilah biasanya kegunaan dari Fungsi Vlookup atau Hlookup.
RUMUS VLOOKUP dan HLOOKUP pada MS.excel
=VLOOKUP[lookup_value,table_array,col_index_num,range_lookup]
=HLOOKUP[lookup_value,table_array,col_index_num,range_lookup]
Lookup Value = adalah nilai dari data yang akan kita ambil, misalnya pada kolom tersebut sumber dari data adalah “ AK” yang nanti akan jadi sebagai AKutansi
Table array = adalah sebuah table sumber yang digunakan untuk mencari sumber dari data tersebut.
Col_ Index = indeks sumber dari data yang kita ambil.
Penyelesaian Rumus VLookup
| RUMUS VLOOKUP dan HLOOKUP pada MS.excel |
Kita selesaikan kasus diatas dengan mencari isi pada Kolom D1 dengan cara menulis pada kolom D1 rumus berikut ini.
=VLOOKUP[C4,$G$4:$H$8,2,]
Setelah itu drag kebawah sehingga hasilnya akan Nampak seperti pada gambar dibawah ini.
| Fungsi Vlookup dan Hlookup pada MS Excel |
Sama persis dengan apa yang kita harapkan dengan menggunakan Vlookup atau Hlookup kasus tersebut terselesaikan dengan sangat mudah. Nah berikut ini ada beberapa hal yang harus kamu ketahui tentang Rumus Vlookup dan Hlookup yaitu perbedaan antara False[Aprocimate] dan True [exact value] pada Lookup.
False = data yang diambil harus sesuai dengan yang ada pada pada sumbernya kalau tidak sesuai maka akan menunjukkan N/A atau noanwser
True= data ini akan mengambil nilai yang paling mirip dengan data yang dimaksud.
Perbedaan VLookup dan Hlookup.
Sebenarnya tidak ada perbedaan yang mencolok antara Vlookup dan Hlookup pada Ms.excel yang membedakan hanya adalah table sumbernya. Dalam VLookup data berupa Vertikal, sedangkan pada Hlookup datanya berupa horizontal.perhatikan gambar dibawah ini untuk mencari perbedaan yang jelas.
| Fungsi Vlookup dan Hlookup pada MS Excel |
HLOOKUP pada Ms.Excel
Mari kita selesaikan kasus pada table diatas dengan menggunakan Fungsi Hlookup, kita sudah berhasil menyelesaikan dengan VLookup. Sebenarnya kita tinggal mengganti Hlookup saja dan mengganti sumber tablenya. Isikan pada Kolom D4 rumus seperti dibawah ini kemudian Drag kebawah dan hasilnya akan seperti pada table dibawah ini.
=HLOOKUP[C4,$G$12:$K$13,2,]
| Hlookup pada MS Excel |
Penting: Kalau menggunakan Rumus Vlookup atau Hlookup harus untuk urusan table sumber harus menggunakan fungsi Absolute, karena kalau tidak menggunakan absolute sewaktu mendrag ke bawah sumber table juga akan bergeser kebawah. Berikut ilustrasinya. Kalau tidak menggunakan tanda absolute atau “$” dollar pada shett.
Hasilnya akan berbeda dengan tanda absolute “$” kalau tanpa absolute hasilnya seperti pada gambar dibawah ini.
| Hlookup pada Ms.excel |
Cara membuat Table Absolute adalah dengan setelah memilih table tekan F4 sehingga nanti semua nama table akan disi oleh tanda $,
Tanpa absolute =VLOOKUP[C4,G4:H8,2,0]
Dengan Absolute = VLOOKUP[C4,$G$4:$H$8,2,0]
Soal dan pembahasan Vlookup dan Hlookup pada Ms.Excel
Soal dan pembahasan Vlookup dan Hlookup pada Ms.Excel |
Isilah Kolom Jadwal Tambahan, Guru dan Biaya pada table diatas berdasarkan data pada dua table dibawah. Pembahasan: kita bisa saja mengisi kolom kolom tersebut secara manual. Akan tetapi ini akan memawakan waktu yang cukup lama, bayangkan jika kita mengisi table tersebut jumlahnya lebih dari satu dan harus dikumpulkan besok pagi. Mari kita gunakan rumus Vlookup dan Hlookup untuk mengisi table tersebut. Dan untuk kolom Jadwal tambahan serta Guru kita gunakan Fungsi Vlookup, sedangkan pada Biaya kita gunakan fungsi Hlookup.
Pada Cell D4 tulislah rumus seperti dibawah ini.
=VLOOKUP[B4,$C$19:$D$22,2,0]
Pada Cell E4 tulislah rumus seperti pada gambar dibawah ini.
=VLOOKUP[B4,$C$19:$E$22,3,0]
Pada Cell F4 tulislah rumus seperti pada gambar dibawah ini.
=HLOOKUP[B4,$B$25:$F$26,2,0]
Setelah itu drag kebawah sehingga hasil akhirnya seperti pada gambar dibawah ini.
| Soal dan pembahasan Vlookup dan Hlookup pada Ms.Excel |
Penggunaan VLOOKUP dan HLOOKUP pada ms.excel yang bersumber beda sheet.
Kadang kita menggunakan fungsi Vlookup dan Hlookup dalam Microsoft excel dan dalam suatu kejadian antara table dan sumber referensinya berbeda sheet satu sama lain, nah untuk kasus seperti ini bagaimana solusinya. Seumpama saja ada dua sheet yaitu sheet yang bernama “ sumber” sebagai sumber nya dan sheet table sebagai tablenya. Seperti pada gambar dibawah ini.
Tabel kita akan mengisi Kolom Jadwal Tambahan, Guru dan Biaya dibawah ini.
| Vlookup dan Hlookup pada ms.excel beda sheet2 |
Dengan sumber dibawah ini.
| Vlookup dan Hlookup pada ms.excel beda sheet |
Cara mengatasi masalah tersebut adalah sama walaupun kita menggunakan sheet yang berbeda tetap saja cara biasa bisa dilakukan. Mari kita isi table pertama diatas dengan sheet “sumber”
1. Tulis pada Sheet Table Cell D4 rumus dibawah ini.
=VLOOKUP[B4,'Sumber '!$B$2:$D$6,2,0]
Caranya walaupun berbeda sheet tetap saja menggunakan rumus sumber vlookup yang sama.
2. Pada sheet table Cell E4 tulis rumus dibawah ini.
=VLOOKUP[B4,'Sumber '!$B$3:$D$6,3,0]
3. Pada kolom Biaya Cell F4 tulislah rumus seperti ini.
=HLOOKUP[B4,'Sumber '!$A$9:$E$10,2,0]
4. Drag ke bawah untuk tiap kolomnya sehingga hasilnya seperti pada gambar dibawah ini.