Cara menggunakan Rumus HLOOKUP dan VLOOKUP MS Excel untuk Pemula

Rumus Vlookup dan Hlookup sebenarnya merupakan rumus yang sama, 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 sebagai referensi pencarian.

Jika data sumber pencarian disajikan dalam bentuk table (range) yang disusun secara vertical (dari atas ke bawah), maka yang digunakan adalah formula VLOOKUP.

Sementara jika data pencarian disajikan 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 agar lebih faham.

Rumus HLOOKUP dan VLOOKUP sering digunakan untuk kasus-kasus tertentu dengan tujuan untuk mempermudah modifikasi data, agar ketika terjadi perubahan atau modifikasi pada data yang dilibatkan dalam formula perhitungan, kita cukup merubah data pada table range yang dijadikan sumber pencarian, sehingga tidak perlu merubah formula perhitungan yang digunakan.

Berikut adalah 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 jika rumus memakai 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 pencarian
• Col_index_num, index colom pada table (range) sumber pencarian  yang nilainya ingin ditampilkan
• Row_index_num, index row pada table (range) sumber pencarian yang nilainya ingin ditampilkan.

Untuk lebih mempermudah pemahanan serta penggunaan VLOOKUP dan HLOOKUP berikut saya sajikan dalam bentuk contoh kasus untuk menghitung gaji karyawan.

Cara menggunakan Rumus HLOOKUP dan VLOOKUP MS Excel untuk Pemula

Contoh kasus, misal kita ingin menghitung gaji karyawan menggunakan microsoft excel, gaji karyawan baik gaji pokok, tunjangan maupun potongan atau pajak, besar kecilnya dipengaruhi oleh golongan karyawan.

Golongan karyawan misalkan saja kita bagi menjadi 3 yait:

• 1A (Gaji Pokok 1.000.000, Tunjangan 150.000 dan potongan pajak 2% dari gaji pokok)
• 1B (Gaji Pokok 2.000.000, Tunjangan 250.000 dan potongan pajak 3% dari gaji pokok)
• 1C (Gaji Pokok 3.000.000, Tunjangan 350.000 dan potongan pajak 4% dari gaji pokok)

Untuk menggunakan HLookup dan VLookup, kita akan sajikan Data gaji pokok dan tunjangan dalam sebuah table (range) yang disusun secara vertical (dari atas ke bawah).

Sementara untuk Potongan, kita akan sajikan dalam table potongan (pajak) yang disajikan dalam bentuk horizontal (dari kiri ke kanan).

Data gaji pokok dan data potongan tersebut nanti akan dijadikan acuan sumber pencarian berdasarkan golongan yang akan dilibatkan dalam formula HLOOKUP dan VLOOKUP.

Terlihat seperti gambar di bawah ini:

Table Komponen gaji disajikan secara Vertical
Dapat dilihat pada table di atas, Table Komponen gaji disajikan dalam bentuk table (range) yang disusun Vertical dari atas ke bawah berdasarkan golongan.(coba saja lihat data angka gaji pokok 1.000.000, 2.000.000 dan 3.000.000 disajikan dari atas ke bawah /Vertical).

Untuk mencari table data gaji pokok, dan data tunjangan kita akan menggunakan table komponen gaji ini menggunakan rumus VLOOKUP dengan Lookup_value adalah golongan.

Table potongan pajak disajikan secara horizontal
Sementara Table potongan 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 menggunakan HLOOKUP.

1. Menampilkan data gaji pokok pada table upah karyawan

Data gaji pokok kita cari di table komponen gaji berdasarkan goolongan menggunakan rumus VLOOKUP, kenapa memakai rumus vlookup, karena table komponen gaji sumber pencarian di atas disajikan secara vertical, sehingga rumusnya menjadi sebagai berikut:

Pada Cell C3, masukan rumus berikut:

=VLOOKUP(B3;$A$11:$C$13;2)

Kemudian Enter, lalu setelah muncul hasilnya drag ke bawah untuk mengisi cell lainnya secara otomatis.

• B3 adalah (golongan setiap karyawan) yang dijadikan kata kunci pencarian pada table komponen gaji.

• $A$11:$C$C13, adalah range A11 sampai C13, yang mendefinisikan table komponen gaji, kita menggunakan tanda $ (absolute) pada cell di range tersebut, tujuannya agar formula range tersebut tidak berubah ketika kita drag.

• Angka 2, adalah angka yang menunjukan index colom gaji pokok pada table komponen gaji, index biasanya dimulai dari 1, di table komponen index colom 1 adalah golongan, index colom 2 adalah gaji pokok dan index colom 3 adalah tunjangan, kita menggunakan nilai 2, karena kita ingin mengambil data gaji pokok di table komponen gaji yang posisinya ada di index kolom ke 2.

2. Menampilkan Tunjangan

Tunjangan kita cari sama pada table Komponen gaji, karena table komponen gaji yang dijadikan sumber pencarian disajikan vertical, kita harus gunakan rumus VLOOKUP, sebagai berikut:

Klik pada Cell D3, lalu masukan rumus

=VLOOKUP(B3;$A$11:$C$13;3)

 Lalu enter kemudian setelah muncul hasilnya drag ke bawah.

• B3 adalah (golongan setiap karyawan) yang dijadikan kata kunci pencarian pada table komponen gaji.

• $A$11:$C$C13, adalah range A11 sampai C13, yang mendefinisikan table komponen gaji, kita menggunakan tanda $ (absolute) pada cell di range tersebut, tujuannya agar formula range tersebut tidak berubah ketika kita drag.

• Angka 3, adalah angka yang menunjukan index colom Tunjangan  pada table komponen gaji, index biasanya dimulai dari 1, di table komponen index colom 1 adalah golongan, index colom 3 adalah tunjangan, kita menggunakan nilai 3, karena kita ingin mengambil data tunjangan di table komponen gaji yang posisinya ada di index kolom ke 3.

 

3. Menampilkan Pajak

Untuk Pajak, table/range yang dijadikan pencarian adalah table Potongan pajak yang disajikan secara Horizontal yang datanya terletak di range B17:D18, sehingga rumus yang digunakan adalah HLOOKUP,  yaitu sebagai berikut:

 Klik pada Cell E3, lalu ketikan rumus

=HLOOKUP(B3;$B$17:$D$18;2)

 Lalu enter, kemudian setelah hasilnya muncul silahkan drag ke bawah. untuk mengisi cell lainnya.

• B3 adalah (golongan setiap karyawan) yang dijadikan kata kunci pencarian pada table komponen gaji.

• $B$17:$C$D18, adalah range B17 sampai D18, yang mendefinisikan table data pajak, kita menggunakan tanda $ (absolute) pada cell di range tersebut, tujuannya agar formula range tersebut tidak berubah ketika kita drag.

• Angka 2 adalah angka yang menunjukan index baris ke 2 (pajak) pada table potongan pajak,  oh ya index biasanya dimulai dari 1, di table potongan pajak kita lihat, index baris 1 adalah golongan, index baris 2 adalah pajak, rumus di atas kita menggunakan nilai 2, karena 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 adalah:

Klik pada Cell F3, kemudian masukan rumus berikut:

=(C3+D3)-((C3+D3)*E3)

Kemudian tekan enter Setelah muncul hasilnya, silahkan  drag ke bawah untuk mengisi cell lainnya pada kolom gaji bersih.

Hasil akhirnya menjadi seperti gambar berikut:

 

Semoga tutorial tentang HLOOKUP dan VLOOKUP di atas dapat anda pahami dan anda bisa menerapkannya pada worksheet anda.

Share this post