Pada waktu mengerjakan rumus VLOOKUP dan HLOOKUP nilai kunci dapat kita lihat pada

HLOOKUP dan VLOOKUP adalah salah satu fungsi pencarian atau referensi, dengan kata lain ketika membutuhkan sebuah data, maka kita bisa memakai VLOOKUP dan HLOOKUP mencari data tersebut dengan menggunakan referensi data yang ada baik yang terletak vertikal maupun horizontal. Penjelasan secara umum mengenai fungsi VLOOKUP dan HLOOKUP adalah sebagai berikut :

1. Fungsi VLOOKUP adalah salah satu fungsi pencarian dalam MS. Excel. VLOOKUP adalah singkatan dari Vertical Lookup, sehingga dapat di artikan Fungsi VLOOKUP adalah suatu fungsi yang di tujukan untuk melakukan pencarian data secara vertikal kolom demi kolom. Adapun Rumus bakunya sebagai berikut :

=VLOOKUP(lookup_value;table_array;row_index_num;[range_lookup])

Atau seperti ini:

=VLOOKUP (nilai_kunci, tabel_data, nomor_kolom,tipe_data)

Penjelasan dari fungsi VLOOKUP diatas

• Nilai_kunci: adalah nilai yang digunakan untuk membaca tabel referensi. Nilai kunci harus ada pada tabel yang akan disi maupun pada tabel referensi.
• Tabel_data: adalah range data yang disusun tegak, berfungsi sebagai tabel bantu/referensi yang akan dibaca. Range data tersebut berisikan data-data yang digunakan untuk mengisi hasil yang diharapkan. Saat anda mengambil tabel_data, pastkan anda tidak menyertakan judul kolomnya.
• Nomor_kolom:adalah 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 baik true ataupun false bisa di gantikan dengan angka 1 (tipe data true) dan 0 (tipe data false.

2. Fungsi HLOOKUP

Fungsi VLOOKUP adalah salah satu fungsi pencarian dalam MS. Excel. HLOOKUP adalah singkatan dari Horizontal Lookup, sehingga dapat di artikan Fungsi HLOOKUP adalah suatu fungsi yang di tujukan untuk melakukan pencarian data secara Horizontal baris demi baris. Adapun Rumus bakunya sebagai berikut :

=HLOOKUP(lookup_value;table_array; row_index_num,[range_lookup])

atau bisa juga di baca sebgai berikut :

=HLOOKUP (nilai_kunci, tabel_data, nomor_baris, tipe_data)

Keterangan penulisan HLOOKUP, diatas:

• Nilai_Kunci : merupakan nilai yang dijadikan dasar untuk membaca tabel acuan pengambilan data. Nilai kunci ini ada dan harus ada, baik pada tabel yang akan diisi maupun pada tabel referensi.
• Tabel_Data : adalah tabel yang menjadi acuan dasar dari data yang akan di cari dan akan di ambil hasilnya
• Nomor_Baris : adalah nomor posisi data pada tabel referensi yang nantinya akan di ambil seluruh datanya, dari baris paling awal sampai baris paling akhir
• Tipe_Data : ada 2 tipe yang bisa digunakan. Tipe data TRUE digunakan, jika nilai datanya tidak jelas/tidak pasti dan tipe data FALSE untuk nilai data yang sudah jelas/ sudah pasti.

Adapun langkah-langkah dalam pembuatan rumus VLOOKUP dan HLOOKUP, yaitu sebagai berikut :

1. Tentukan Nilai Kunci. Nilai kunci adalah nilai yang menjadi acuan ketepatan dalam pengambilan data nantinya, sehingga sudah pasti nilai kunci akan ada baik pada tabel referensi maupun tabel yang akan di isi hasilnya.
2. Tentukan Range dari tabel data yang akan di jadikan acuan pengambilan datanya
3. Setelah menentukan range dari tabel data, maka kita harus menentukan letak dari data yang akan kita ambil, terletak pada urutan ke berapa kolom atau baris yang akan kita ambil datanya.
4. Tentukan tipe datanya, apabila datanya sudah pasti berikan nilai data False dan bila belum pasti berikan nilai False.

Langkah-langkah Menggunakan Rumus VLOOKUP dan HLOOKUP (Lengkap Dengan Contoh)

Contoh soal dari langkah-langkah tersebut adalah sebagai berikut :

Membuat data besar gaji, tunjangan makan, tunjangan transportasi dan gaji bersih dari masing-masing 5 karyawan PT. ABC. Data di ambil data besar gaji dan pajak pergolongan karyawan. Datanya sebagai berikut :

Untuk menghitung gaji pokok kita gunakan rumus VLOOKUP, langkah-langkahnya sebagai berikut :

a. Tentukan Nilai Kunci.

Kita ingat rumus baku dari VLOOKUP adalah

=VLOOKUP (nilai_kunci, tabel_data, nomor_kolom,tipe_data)

Tentukan data mana yang akan menjadi data dasar pencarian kita. Di dalam soal kita yang menjadi nilai kunci adalah Gol, karena hal itu menjadi dasar dalam pemberian gaji, tunjangan makan, transportasi. Kita ambil alamat Cell pada tabel yang akan kita isi yaitu B9, B10, dstnya

Setelah menemukan Nilai kuncinya kita terapkan dalam rumus. Maka rumus VLOOKUP-nya menjadi :

=VLOOKUP(B9;tabel_data;nomor_kolom;tipe_data)

b. Tentukan Range tabel data yang akan menjadi bahan referensi dari pencarian kita.

Di dalam soal yang menjadi range dari tabel data yang akan kita ambil yaitu dengan data range B3:E5, karena data ini memuat besaran gaji pokok, tunjangan makan dan transportasi dari tiap golongan.

Tampilannya :

Setelah menetapkan tabel data-nya, range datanya yaitu B3:E5, kita masukkan dalam rumus. Maka rumus VLOOKUP-nya menjadi :

=VLOOKUP(B9;B3:E5;nomor_kolom;tipe_data)

c. Masukkan nomor kolom dimana terdapat range data yang ingin kita ambil datanya.

Kita harus menentukan letak urutan kolom di mana terdapat range data yang kita inginkan. Di dalam soal dapat kita lihat no urutan atau nomor kolom dimana terdapat data gaji pokok yaitu Nomor Kolom 2, seperti tampilan berikut :

Setelah menetapkan Nilai Kolom yaitu kolom 2 kita masukkan dalam rumus. Maka rumus VLOOKUP-nya menjadi :

=VLOOKUP(B9;B3:E5;2;tipe_data)

d. Tentukan Tipe data.

Untuk penggunaan tipe data, yang kita gunakan adalah tipe data FALSE, hal ini karena data yang ada adalah data pasti seperti Gaji untuk karyawan Gol 1A pasti 900.000.

Setelah menetapkan Tipe data yang akan di gunakan, yaitu FALSE, kita masukkan dalam rumus. Maka rumus lengkap VLOOKUP-nya menjadi :

=VLOOKUP(B9;B3:E5;2;FALSE)

agar letak kolomnya tetap terikat maka di depan alamat kolom dan alamat baris (pada bagian Nilai Kunci hanya di depan alamat Kolom) kita ketik tanda $, sehingga rumusnya menjadi :

=VLOOKUP($B9;$B$3:$E$5;2;FALSE)

setelah itu tinggal kita drag salin dengan menyorot tanda (+)sebelah kiri bawah Cell, maka hasilnya seperti tampilan berikut :

2. Menghitung Tunjangan Makan.

Untuk menghitung Tunjangan Makan maka kita ulangi langkah-langkah seperti untuk menghitung gaji pokok. Pada dasarnya rumusnya sama, perbedaannya hanya pada nomor kolom. Rumusnya sendiri sebagai berikut :

=VLOOKUP($B9;$B$3:$E$5;3;FALSE)

Tampilan hasilnya menjadi sebagai berikut:

3. Menghitung Tunjangan Transportasi

Sama seperti untuk menghitung gaji pokok dan Tunjangan Makan kita ulangi langkah-langkahnya. Pada dasarnya rumusnya sama, perbedaannya hanya pada nomor kolom. Rumusnya sendiri sebagai berikut :

=VLOOKUP($B9;$B$3:$E$5;4;FALSE)

Tampilan hasilnya menjadi sebagai berikut:

4. Menghitung Total Gaji

Untuk menghitung Total gaji karyawan, maka kita bisa menggunakan rumus SUM. Caranya adalah pada Cell G9 kita ketikkan rumus di bawah ini :

=SUM(D9:F13)

Setelah itu tekan enter, hasilnya menjadi seperti ini :

kemudian klik tahan tanda (+) dibagian sebelah kanan bawah Cell G9, lalu drag/salin hingga ke Cell G13, maka hasilnya menjadi seperti ini :

5. Menghitung Besar Nilai Pajak Di Kenakan

Untuk menghitung pajak maka kita akan menggunakan rumus HLOOKUP. Langkah-langkah pembuatan rumusnya pada dasarnya sama dengan pembuatan rumus VLOOKUP, perbedaannya adalah pada rumus VLOOKUP menggunakan Nomor Kolom, sedangkan pada HLOOKUP menggunakan Nomor Baris, sehingga Rumus bakunya menjadi :

=HLOOKUP (nilai_kunci, tabel_data, nomor_baris, tipe_data)

Adapun rincian langkah-langkahnya sebagai berikut :

Sama seperti pada pengambilan kunci untuk penghitungan Gaji Pokok, Tunjangan Makan dan Transportasi, nilai kunci yang di gunakan adalah Gol yang terletak pada tabel Gaji Bersih Karyawan dan Tabel Potongan Pajak.

Setelah menemukan Nilai kuncinya kita terapkan dalam rumus. Maka rumus HLOOKUP-nya menjadi :

=HLOOKUP(B9;tabel_data;nomor_kolom;tipe_data)

b. Tentukan Range tabel data yang akan menjadi bahan referensi dari pencarian kita.

Di dalam soal yang menjadi range dari tabel data yang akan kita ambil untuk perhitungan besaran pajak yaitu dengan data range G2:I3, karena data ini memuat besaran gaji pokok, tunjangan makan dan transportasi dari tiap golongan.

Setelah menetapkan tabel data-nya, range datanya yaitu G1:I2, kita masukkan dalam rumus. Maka rumus HLOOKUP-nya menjadi :

=HLOOKUP(B9;G2:I3;nomor_kolom;tipe_data)

c. Masukkan nomor kolom dimana terdapat range data yang ingin kita ambil datanya.

Kita harus menentukan letak urutan baris di mana terdapat range data yang kita inginkan. Di dalam soal dapat kita lihat no urutan atau nomor baris dimana terdapat data besaran potongan gaji yaitu Barisan Nomor 2

Setelah menetapkan Nomor Baris yaitu Baris 2, kita masukkan dalam rumus, Maka rumus HLOOKUP-nya menjadi :

=HLOOKUP(B9;G2:I3;2;tipe_data)

d. Tentukan Tipe data.

Untuk penggunaan tipe data, yang kita gunakan adalah tipe data FALSE, hal ini karena data yang ada adalah data pasti seperti potongan gaji untuk karyawan Gol 1A pasti 2%

Setelah menetapkan Tipe data yang akan di gunakan, yaitu FALSE, kita masukkan dalam rumus. Maka rumus lengkap HLOOKUP-nya menjadi :

=HLOOKUP(B9;G2:I3;2;FALSE)

agar letak kolomnya tetap terikat maka di depan alamat kolom dan alamat baris (pada bagian Nilai Kunci hanya di depan alamat Kolom) kita ketik tanda $, sehingga rumusnya menjadi :

=HLOOKUP($B9;$G$2:$I$3;2;FALSE)

Agar mendapatkan Besaran Nilai Potongan Pajak maka perlu di kalikan dengan Nilai Total Gaji Masing-masing karyawan. Kalau di buat dalam bentuk rumus menjadi :

=HLOOKUP($B9;$G$2:$I$3;2;FALSE)*G9

Maka hasilnya menjadi seperti ini :

kemudian klik tahan tanda (+) dibagian sebelah kanan bawah Cell H9, lalu drag/salin hingga ke Cell H13, maka hasilnya menjadi seperti ini :

6. Menghitung Gaji Bersih

Perhitungan Gaji Bersih di lakukan dengan mengurangi Total Gaji (Cell G9) dengan Besaran Potongan Pajak yang di kenakan (Cell H9), kalau di buat rumus maka menjadi sebagai berikut :

=G9-H9

masukkan rumus itu di Cell I9 lalu tekan Enter, maka hasilnya menjadi seperti ini :

kemudian klik tahan tanda (+) dibagian sebelah kanan bawah Cell I9, lalu drag/salin hingga ke Cell I13, maka hasilnya menjadi seperti ini :

7. Menghitung Total Gaji Pokok Tunjangan Makan, Tunjangan Tranportasi, Total keseluruhan Gaji, Total Potongan Pajak, dan Total Gaji Bersih

Untuk menghitung Jumlah Total Keselurhan Gaji Pokok, Tunjangan Makan, Tunjangan Transportasi, Total keseluruhan Gaji, Total Potongan Pajak, dan Total Gaji Bersih, maka ketikkan rumus :

=SUM(D9:D13)

Di Cell D14, maka hasilnya menjadi seperti ini :

Setelah itu klik tahan tanda (+) dibagian sebelah kanan bawah Cell I9, lalu drag/salin hingga ke Cell I13, maka hasilnya menjadi seperti ini:

Pada tampilan di atas besaran nilai gaji pokok di awali dengan tanda (Rp), hal ini bisa di lakukan dengan cara :

• Klik dan sorot Cell D9 s.d Cell I14
• Setelah itu Klik Kanan Pilih Tab Format Cell
• Setelah Halaman Format Cells terbuka pada tab Number, pada tab Category pilih Accounting, pada Tab Decimals buat nilainya menjadi 0 (Nol) Pada Tab Symbol pilih Simbol Rupiah (Rp), setelah itu tekan OK. Tampilannya seperti berikut ini :

• Hasilnya menjadi seperti ini :