Rumus Vlookup dan Hlookup bergotong-royong ialah rumus yang serupa, fungsi rumus tersebut adalah untuk mencari data yang bersumber dari data yang disajikan dalam sebuah table atau range di dalam worksheet microsoft excel, perbedaan kedua rumus tersebut terletak pada sumber data table (range) yang digunakan selaku rujukan penelusuran.
Jika data sumber penelusuran dihidangkan dalam bentuk table (range) yang disusun secara vertical (dari atas ke bawah), maka yang dipakai yakni formula VLOOKUP.
Sementara jika data pencarian disuguhkan dalam bentuk table /range yang disusun secara Horizontal (dari kiri ke kanan), maka rumus yang digunakan rumus HLOOKUP.
Anda bisa lihat gambar-gambar tabel di bawah semoga lebih faham.
Rumus HLOOKUP dan VLOOKUP sering dipakai untuk kasus-kasus tertentu dengan tujuan untuk mempermudah penyesuaian data, agar saat terjadi pergantian atau penyesuaian pada data yang dilibatkan dalam formula perhitungan, kita cukup mengganti data pada table range yang dijadikan sumber penelusuran, sehingga tidak perlu merubah formula perkiraan yang dipakai.
Berikut ialah format Rumus VLOOKUP dan HLOOKUP pada microsoft excel:
Oh ya kalau rumus menggunakan tanda koma (,) di atas tidak jalan di komputer anda, ganti tanda koma dengan titik koma (;)
Keterangan:
Golongan karyawan misalkan saja kita bagi menjadi 3 yait:
Sementara untuk Potongan, kita akan hidangkan dalam table cuilan (pajak) yang disajikan dalam bentuk horizontal (dari kiri ke kanan).
Data honor pokok dan data potongan tersebut nanti akan dijadikan acuan sumber pencarian berdasarkan golongan yang hendak dilibatkan dalam formula HLOOKUP dan VLOOKUP.
Terlihat mirip gambar di bawah ini:
Table Komponen gaji disuguhkan secara Vertical
Dapat dilihat pada table di atas, Table Komponen honor disajikan dalam bentuk table (range) yang disusun Vertical dari atas ke bawah berdasarkan kelompok.(coba saja lihat data angka honor pokok 1.000.000, 2.000.000 dan 3.000.000 disuguhkan dari atas ke bawah /Vertical).
Untuk mencari table data gaji pokok, dan data dukungan kita akan menggunakan table unsur honor ini menggunakan rumus VLOOKUP dengan Lookup_value adalah kelompok.
Table pecahan pajak dihidangkan secara horizontal
Sementara Table serpihan pajak, kita buat dalam bentuk table (range) horizontal dari kiri ke kanan, (lihat saja Pajak: 2%, 3%, 4%, disajikan ke kanan/horizontal), sehingga untuk mencari persentase pajak pada table ini kita akan memakai HLOOKUP.
Pada Cell C3, masukan rumus berikut:
Klik pada Cell D3, kemudian masukan rumus
Klik pada Cell E3, lalu ketikan rumus
Klik pada Cell F3, lalu masukan rumus berikut:
Hasil karenanya menjadi seperti gambar berikut:
Jika data sumber penelusuran dihidangkan dalam bentuk table (range) yang disusun secara vertical (dari atas ke bawah), maka yang dipakai yakni formula VLOOKUP.
Sementara jika data pencarian disuguhkan dalam bentuk table /range yang disusun secara Horizontal (dari kiri ke kanan), maka rumus yang digunakan rumus HLOOKUP.
Anda bisa lihat gambar-gambar tabel di bawah semoga lebih faham.
Rumus HLOOKUP dan VLOOKUP sering dipakai untuk kasus-kasus tertentu dengan tujuan untuk mempermudah penyesuaian data, agar saat terjadi pergantian atau penyesuaian pada data yang dilibatkan dalam formula perhitungan, kita cukup mengganti data pada table range yang dijadikan sumber penelusuran, sehingga tidak perlu merubah formula perkiraan yang dipakai.
Berikut ialah format Rumus VLOOKUP dan HLOOKUP pada microsoft excel:
=VLOOKUP(lookup_value, table_array, col_index_num)
=HLOOKUP(lookup_value, table_array, row_index_num)
Oh ya kalau rumus menggunakan tanda koma (,) di atas tidak jalan di komputer anda, ganti tanda koma dengan titik koma (;)
Keterangan:
- Lookup_value= nilai yang dijadikan kriteria pencarian
- Table_array=range yang berisi data untuk sumber penelusuran
- Col_index_num, index colom pada table (range) sumber penelusuran yang nilainya ingin ditampilkan
- Row_index_num, index row pada table (range) sumber pencarian yang nilainya ingin ditampilkan.
Cara memakai Rumus HLOOKUP dan VLOOKUP MS Excel untuk Pemula
Contoh kasus, misal kita ingin menjumlah gaji karyawan menggunakan microsoft excel, gaji karyawan baik gaji pokok, tunjangan maupun potongan atau pajak, besar kecilnya dipengaruhi oleh kalangan karyawan.Golongan karyawan misalkan saja kita bagi menjadi 3 yait:
- 1A (Gaji Pokok 1.000.000, Tunjangan 150.000 dan bagian pajak 2% dari gaji pokok)
- 1B (Gaji Pokok 2.000.000, Tunjangan 250.000 dan cuilan pajak 3% dari gaji pokok)
- 1C (Gaji Pokok 3.000.000, Tunjangan 350.000 dan potongan pajak 4% dari honor pokok)
Sementara untuk Potongan, kita akan hidangkan dalam table cuilan (pajak) yang disajikan dalam bentuk horizontal (dari kiri ke kanan).
Data honor pokok dan data potongan tersebut nanti akan dijadikan acuan sumber pencarian berdasarkan golongan yang hendak dilibatkan dalam formula HLOOKUP dan VLOOKUP.
Terlihat mirip gambar di bawah ini:
Table Komponen gaji disuguhkan secara Vertical
Dapat dilihat pada table di atas, Table Komponen honor disajikan dalam bentuk table (range) yang disusun Vertical dari atas ke bawah berdasarkan kelompok.(coba saja lihat data angka honor pokok 1.000.000, 2.000.000 dan 3.000.000 disuguhkan dari atas ke bawah /Vertical).
Untuk mencari table data gaji pokok, dan data dukungan kita akan menggunakan table unsur honor ini menggunakan rumus VLOOKUP dengan Lookup_value adalah kelompok.
Table pecahan pajak dihidangkan secara horizontal
Sementara Table serpihan pajak, kita buat dalam bentuk table (range) horizontal dari kiri ke kanan, (lihat saja Pajak: 2%, 3%, 4%, disajikan ke kanan/horizontal), sehingga untuk mencari persentase pajak pada table ini kita akan memakai HLOOKUP.
1. Menampilkan data honor pokok pada table upah karyawan
Data gaji pokok kita cari di table unsur honor menurut goolongan menggunakan rumus VLOOKUP, kenapa menggunakan rumus vlookup, sebab table komponen gaji sumber pencarian di atas disuguhkan secara vertical, sehingga rumusnya menjadi sebagai berikut:Pada Cell C3, masukan rumus berikut:
=VLOOKUP(B3;$A$11:$C$13;2)Kemudian Enter, kemudian setelah muncul karenanya drag ke bawah untuk mengisi cell yang lain secara otomatis.
- B3 adalah (kelompok setiap karyawan) yang dijadikan keyword pencarian pada table bagian honor.
- $A$11:$C$C13, ialah range A11 sampai C13, yang mendefinisikan table unsur honor, kita menggunakan tanda $ (absolute) pada cell di range tersebut, tujuannya biar formula range tersebut tidak berganti saat kita drag.
- Angka 2, ialah angka yang membuktikan index colom honor pokok pada table unsur gaji, index biasanya dimulai dari 1, di table bagian index colom 1 adalah golongan, index colom 2 yaitu gaji pokok dan index colom 3 yakni tunjangan, kita memakai nilai 2, sebab kita ingin mengambil data honor pokok di table bagian honor yang posisinya ada di index kolom ke 2.
2. Menampilkan Tunjangan
Tunjangan kita cari sama pada table Komponen gaji, alasannya table komponen gaji yang dijadikan sumber pencarian disuguhkan vertical, kita harus gunakan rumus VLOOKUP, selaku berikut:Klik pada Cell D3, kemudian masukan rumus
=VLOOKUP(B3;$A$11:$C$13;3)Lalu enter kemudian sesudah timbul balasannya drag ke bawah.
- B3 yakni (golongan setiap karyawan) yang dijadikan keyword penelusuran pada table bagian honor.
- $A$11:$C$C13, yaitu range A11 hingga C13, yang mendefinisikan table bagian gaji, kita menggunakan tanda $ (absolute) pada cell di range tersebut, maksudnya supaya formula range tersebut tidak berubah ketika kita drag.
- Angka 3, ialah angka yang mengambarkan index colom Tunjangan pada table komponen gaji, index biasanya dimulai dari 1, di table bagian index colom 1 yakni kalangan, index colom 3 ialah derma, kita menggunakan nilai 3, alasannya kita ingin mengambil data pertolongan di table komponen honor yang posisinya ada di index kolom ke 3.
3. Menampilkan Pajak
Untuk Pajak, table/range yang dijadikan penelusuran yakni table Potongan pajak yang disajikan secara Horizontal yang datanya terletak di range B17:D18, sehingga rumus yang dipakai ialah HLOOKUP, yakni sebagai berikut:Klik pada Cell E3, lalu ketikan rumus
=HLOOKUP(B3;$B$17:$D$18;2)Lalu enter, lalu sehabis jadinya muncul silahkan drag ke bawah. untuk mengisi cell lainnya.
- B3 yakni (kalangan setiap karyawan) yang dijadikan kata kunci penelusuran pada table unsur honor.
- $B$17:$C$D18, adalah range B17 sampai D18, yang mendefinisikan table data pajak, kita memakai tanda $ (absolute) pada cell di range tersebut, tujuannya agar formula range tersebut tidak berubah dikala kita drag.
- Angka 2 yakni angka yang menunjukan index baris ke 2 (pajak) pada table serpihan pajak, oh ya index umumnya dimulai dari 1, di table serpihan pajak kita lihat, index baris 1 yakni golongan, index baris 2 ialah pajak, rumus di atas kita menggunakan nilai 2, alasannya adalah kita ingin mengambil data pajak di table potongan pajak yang posisinya ada di index baris ke 2.
4. Menampilkan Gaji bersih
Gaji bersih rumusnya Gaji Pokok + Tunjangan -((Gaji pokok+Tunjangan)*pajak). sehingga rumusnya yaitu:Klik pada Cell F3, lalu masukan rumus berikut:
=(C3+D3)-((C3+D3)*E3)Kemudian tekan enter Setelah muncul jadinya, silahkan drag ke bawah untuk mengisi cell lainnya pada kolom honor bersih.
Hasil karenanya menjadi seperti gambar berikut:
Semoga panduan wacana HLOOKUP dan VLOOKUP di atas mampu anda pahami dan anda bisa menerapkannya pada worksheet anda.