Rumus VLOOKUP HLOOKUP merupakan rumus Excel yang digunakan memproses informasi, menganalisa konten, melakukan perhitungan, pengujian kondisi, dan lain sebagainya. Hal ini tentunya akan mempermudah bagi pengguna, terutama dalam hal data dan statistik.
Cara Menggunakan Rumus VLOOKUP dan HLOOKUP
Rumus VLOOKUP berfungsi untuk menampilkan data dari sebuah table yang disusun dalam format vertikal. Berikut bentuk penulisan fungsinya =VLOOKUP (lookup_value, tabel_array,col_index_num).
Sementara rumus HLOOKUP digunakan untuk menampilkan data dari sebuah tabel yang disusun dalam format horizontal.
Berikut bentuk penulisan fungsinya=HLOOKUP(lookup_value,tabel_array,row_index_num).
Contoh Pengaplikasian VLOOKUP dan HLOOKUP
| DAFTAR GAJI PEGAWAI PT. USAHA RIPUH | ||||||||
| Bagian Operator | ||||||||
| No | Gol | Nama | Gaji | Total Gaji | Pajak | Gaji Bersih | ||
| Pegawai | Gaji Pokok | Tunjangan | Transportasi | |||||
| 1 | 1C | Asep | ||||||
| 2 | 1B | Odih | ||||||
| 3 | 1B | Wawan | ||||||
| 4 | 1C | Siti | ||||||
| 5 | 1A | Rodiyah | ||||||
| Total | ||||||||
| Tabel Gaji | Tabel Potongan Pajak | |||||||
| Gol | Gaji Pokok | Tunjangan | Transportasi | 1A | 1B | 1C | ||
| 1A | 700.000 | 50.000 | 100.000 | 3% | 4% | 5% | ||
| 1B | 800.000 | 60.000 | 100.000 | |||||
| 1C | 1.000.000 | 100.000 | 100.000 | |||||
Dengan merujuk pada contoh soal di atas, ada beberapa hal yang harus anda lakukan yaitu menghitung gaji pokok, tunjangan, transportasi, total gaji, gaji bersih, dan total.
- Menghitung Gaji Pokok
Untuk menghitung gaji pokok anda gunakan rumus VLOOKUP di sel D5. Jika sudah mengisi formula di sel D5, anda copy formula/drag hingga ke sel D9. Bentuk penulisannya seperti yang sudah dijelaskan di atas, yakni =VLOOKUP (lookup_value, tabel_array,col_index_num).
Sebagai catatan, Ilookup_value, anda isi dengan nilai sebagai dasar (nilai kunci) pembacaan tabel data. Sementara tabel_array merupakan range data yang disusun vertikal yang berfungsi sebagai tabel bantu yang akan dibaca. Sedangkan col_index_num, merupakan nomor urut kolom yang berfungsi untuk pembacaan yang dimulai dari kolom paling kiri.
Selain itu, lantaran posisi “tabel gaji” yang akan dibaca posisinya tetap. maka guna menghindari eror ketika di copy formula/drag maka anda tambahkan $, untuk mengabsolutkan sel. Berikut cara pengerjaannya:
| DAFTAR GAJI PEGAWAI PT. USAHA RIPUH | |||||||||||
| Bagian Operator | |||||||||||
| No | Gol | Nama | Gaji | Total Gaji | Pajak | Gaji Bersih | |||||
| Pegawai | Gaji Pokok | Tunjangan | Transportasi | ||||||||
| 1 | 1C | Asep | =VLOOKUP(B5,$B$14:SE16,2) | ||||||||
| 2 | 1B | Odih | |||||||||
| 3 | 1B | Wawan | |||||||||
| 4 | 1C | Siti | |||||||||
| 5 | 1A | Rodiyah | |||||||||
| Total | |||||||||||
| Tabel Gaji | Tabel Potongan Pajak | ||||||||||
| Gol | Gaji Pokok | Tunjangan | Transportasi | 1A | 1B | 1C | |||||
| 1A | 700.000 | 50.000 | 100.000 | 3% | 4% | 5% | |||||
| 1B | 800.000 | 60.000 | 100.000 | ||||||||
| 1C | 1.000.000 | 100.000 | 100.000 | ||||||||
- Menghitung Tunjangan
Gunakan VLOOKUP di sel E5 untuk menghitung tunjangan. Usai isi formulir di sel 5, copy formula atau drag sampai sel E9
| DAFTAR GAJI PEGAWAI PT. USAHA RIPUH | ||||||||
| Bagian Operator | ||||||||
| No | Gol | Nama | Gaji | Total Gaji | Pajak | Gaji Bersih | ||
| Pegawai | Gaji Pokok | Tunjangan | Transportasi | |||||
| 1 | 1C | Asep |
=VLOOKUP(B5,$B$14:SE16,3) |
|||||
| 2 | 1B | Odih | ||||||
| 3 | 1B | Wawan | ||||||
| 4 | 1C | Siti | ||||||
| 5 | 1A | Rodiyah | ||||||
| Total | ||||||||
| Tabel Gaji | Tabel Potongan Pajak | |||||||
| Gol | Gaji Pokok | Tunjangan | Transportasi | 1A | 1B | 1C | ||
| 1A | 700.000 | 50.000 | 100.000 | 3% | 4% | 5% | ||
| 1B | 800.000 | 60.000 | 100.000 | |||||
| 1C | 1.000.000 | 100.000 | 100.000 | |||||
- Menghitung Total Gaji
Jika untuk menghitung total gaji, anda gunakan fungsi SUM di sel G5 dan tulis rumusnya yaitu =SUM(D5:F5)
Usai memasukan rumus di sel G5, anda salin rumus ke sel G9. Ate, anda juga bisa menambahkannya ke sel G5, seperti berikut ini: =D5+E5+F5, kemudian salin ke bawah.
| DAFTAR GAJI PEGAWAI PT. USAHA RIPUH | |||||||||
| Bagian Operator | |||||||||
| No | Gol | Nama | Gaji | Total Gaji | Pajak | Gaji Bersih | |||
| Pegawai | Gaji Pokok | Tunjangan | Transportasi | ||||||
| 1 | 1C | Asep |
= VLOOKUP(B5,$BS14:$E16,4 |
||||||
| 2 | 1B | Odih | |||||||
| 3 | 1B | Wawan | |||||||
| 4 | 1C | Siti | |||||||
| 5 | 1A | Rodiyah | |||||||
| Total | |||||||||
| Tabel Gaji | Tabel Potongan Pajak | ||||||||
| Gol | Gaji Pokok | Tunjangan | Transportasi | 1A | 1B | 1C | |||
| 1A | 700.000 | 50.000 | 100.000 | 3% | 4% | 5% | |||
| 1B | 800.000 | 60.000 | 100.000 | ||||||
| 1C | 1.000.000 | 100.000 | 100.000 | ||||||
- Menghitung Pajak
Untuk menghitung pajak anda gunakan fungsi HLOOKUP di sel H5, lalu copy formula atau seret hingga sel H9.
| DAFTAR GAJI PEGAWAI PT. USAHA RIPUH | ||||||||
| Bagian Operator | ||||||||
| No | Gol | Nama | Gaji | Total Gaji | Pajak | Gaji Bersih | ||
| Pegawai | Gaji Pokok | Tunjangan | Transportasi | =HLOOKUP
(B5,$G$13: $I$14,2)*D5
|
||||
| 1 | 1C | Asep | ||||||
| 2 | 1B | Odih | ||||||
| 3 | 1B | Wawan | ||||||
| 4 | 1C | Siti | ||||||
| 5 | 1A | Rodiyah | ||||||
| Total | ||||||||
| Tabel Gaji | Tabel Potongan Pajak | |||||||
| Gol | Gaji Pokok | Tunjangan | Transportasi | 1A | 1B | 1C | ||
| 1A | 700.000 | 50.000 | 100.000 | 3% | 4% | 5% | ||
| 1B | 800.000 | 60.000 | 100.000 | |||||
| 1C | 1.000.000 | 100.000 | 100.000 | |||||
- Menghitung Gaji Bersih
Untuk menghitung gaji bersih gunakan fungsi SUM di sel 15 seperti ini =SUM(G5-G5), kemudian copy formula/drag sampai sel 19. Anda juga bisa menggunakan perintah =G5-H5, copy atau drag ke bawah hingga range 19.
Langkah yang terakhir adalah menghitung “total” masing-masing bagian yang telah anda kerjakan sebelumnya. Caranya adalah menggunakan fungsi SUM di sel D10, lalu copy formula atau seret ke samping kanan hingga ke I10.