Cara Menggunakan Fungsi VLOOKUP Excel

Fungsi Vlookup

Jika Anda ingin membuat suatu isian data yang dapat terisi secara otomatis di Excel menggunakan fungsi Vlookup, pertama-tama, Anda perlu membuat data base (data referensi) terlebih dulu di Excel.

Untuk menghubungkan data base (data referensi) dengan sel yang dapat terisi secara otomatis, Anda perlu menggunakan rumus Vlookup.

Read More

Vlookup merupakan fungsi Excel yang aturan penulisannya sebagai berikut:

VLOOKUP (nilai_lookup, tabel_array, nomor_index_kolom, jangkauan_lookup);

Keterangan variable pada rumus Vlookup:

nilai_lookup : Nilai yang ingin dicari sesuai data yang tertera pada table referensi.

tabel_array: Merupakan data-data yang terdiri dari data referensi.

nomor_index_kolom: kolom pada tabel referensi yang akan diambil datanya sebagai isian otomatis ketika pasangan data dipilih.

jangkauan_lookup: Kriteria lookup bisa bernilai FALSE atau TRUE. Jika Anda mengosongkan pilihan ini, maka secara default akan bernilai TRUE. Anda juga bisa mengganti FALSE atau TRUE dengan angka 0 atau 1.

FALSE digunakan jika data yang ingin dicari / pasangan data harus sama persis dengan data yang diinginkan (sesuai dengan pasangannya). Jika pasangan data tidak sesuai akan muncul pesan error.

TRUE digunakan jika data yang dicari tidak harus sesuai dengan data yang diinginkan dan bisa digunakan data alternatif (perkiraan).

Contoh Penggunaan Fungsi Vlookup

Contoh 1 :

Fungsi Vlookup

Pada gambar di atas, terdapat 2 jenis table, yaitu table database (data referensi) dan table data isian (Sales Data).

Pada contoh table di atas, data dari G3 sampai G5 diisi dengan formula Vlookup berikut:

G3 = VLOOKUP(F3,Product_Reference,2,FALSE)

G4 = VLOOKUP(F4,Product_Reference,2,FALSE)

G5 = VLOOKUP(F5,Product_Reference,2,FALSE)

Formula Vlookup

Pada kolom G data akan terisi secara otomatis sesuai dengan pasangan data yang diisi secara manual pada kolom F.

Sedangkan data dari H3 sampai H5 diisi dengan formula Vlookup berikut:

H3 = VLOOKUP(F3,Product_Reference,3,FALSE)

H4 = VLOOKUP(F4,Product_Reference,3,FALSE)

H5 = VLOOKUP(F5,Product_Reference,3,FALSE)

Pada kolom H data akan terisi secara otomatis sesuai dengan pasangan data yang diisi secara manual pada kolom F.

Anda bisa lihat pasangan data pada table referensi.

Pada contoh di atas data referensi berada pada area $B$3:$D$7. Diberi nama (name range) Product_Reference.

Baca juga cara membuat nama sekelompok area sel di Excel dengan Name Manager.

Contoh 2 (penggunaan jangkauan_lookup = TRUE (1)) :

Jika Anda diminta untuk membuat membuat tabel perhitungan tarif pajak penghasilan dengan menggunakan data referensi berikut:

Tingkat PenghasilanTarif Pajak
$0-$9,99915%
$10,000-$29,99930%
$30,000-$99,00034%
$100,000 atau lebih40%

Maka, Anda bisa menggunakan fungsi Vlookup untuk menghitungnya.

Caranya sebagai berikut:

Dari tabel di atas Anda bisa membuat tabel referensi data di Excel seperti di bawah ini:

IncomeTax rate
00.15
100000.3
300000.34
1000000.4

Jika dibuat dalam Excel tabel di atas bisa dibuat menjadi:

Dari tabel di atas maka dapat diartikan sebagai berikut:

  • Orang dengan penghasilan dibawah $ 10.000 akan terkena pajak 15% (0.15)
  • Orang dengan penghasilan $ 10.000 sampai di bawah $ 30.000 akan terkena pajak pendapatan 30 % (0.3)
  • Orang dengan penghasilan $ 30.000 sampai di bawah $ 100.000 akan terkena pajak pendapatan 34% (0.34)
  • Orang dengan penghasilan $ 100.000 atau lebih akan terkena pajak pendapatan 40% (0.4).

Data referensi berada pada area D6:E9 dan diberi nama Tax_Reference.

Selanjutnya dibuat tabel untuk pengisian data dengan formula sebagai berikut:

Pada tabel di atas formula pada sel O4 sampai O8 sebagai berikut:

O4 = VLOOKUP(N4,Tax_Reference,2,1)

O5 = VLOOKUP(N5,Tax_Reference,2,1)

O6 = VLOOKUP(N6,Tax_Reference,2,1)

O7 = VLOOKUP(N7,Tax_Reference,2,1)

O8 = VLOOKUP(N8,Tax_Reference,2,1)

Catatan: Nilai TRUE pada formula di atas diwakili oleh angka 1.

Selanjutnya data penghasilan pada kolom income diisi secara manual, hasilnya sebagai berikut:

Terlihat bagian Tax rate secara otomatis terisi sesuai dengan kriteria penghasilan dan pajak pada (lihat data referensi) yang harus di bayar.

Perbedaa TRUE dan FALSE pada Fungsi Vlookup pada Contoh 2

Berikut ini perbedaan jika Anda menggunakan FALSE (0) dan TRUE (1) pada jangkauan_lookup:

D6:E9 dan diberi nama Tax_Reference.

Jika diperhatikan tabel di atas jika Anda menggunakan TRUE maka data diambil dari rentang data yang ada. Sedangkan jika menggunakan FALSE maka data yang diambil harus persis sama dengan pasangannya, jika tidak akan muncul pesan #N/A.

Maka, pada contoh 2 ini Anda harus menggunakan jangkauan_lookup=TRUE pada fungsi Vlookup.

Anda bisa tonton video di bawah agar lebih jelas:

Penggunaan Fungsi Hlookup di Excel

H pada fungsi Hlookup Excel memiliki kepanjangan Horizontal. Fungsi ini memiliki kemiripan dalam penulisan rumus dengan Vlookup.

Bedanya, jika Vlookup membandingkan data referensi secara vertikal, Hlookup membandingkan data referensi secara horisontal.

Hlookup merupakan fungsi Excel yang aturan penulisannya sebagai berikut:

HLOOKUP (nilai_lookup, tabel_array, nomor_index_baris, jangkauan_lookup);

Keterangan pada rumus Hlookup hampir sama dengan Vlookup, bedanya:

nomor_index_baris : merupakan nomor baris pada tabel data referensi (bukan nomor kolom).

Contoh penggunaan Hlookup:

Saya menampilkan contoh yang hampir sama dengan penggunaan Vlookup. Bedanya data pada tabel referensi diubah (transpose) dari yang susunannya vertikal menjadi horisontal.

Maka, penulisan rumus pada tabel isian dibuat sebagai berikut:

D3 = HLOOKUP(C3,HLOOKUP_Product,2,0)

D4 = HLOOKUP(C4,HLOOKUP_Product,2,0)

D5 = HLOOKUP(C5,HLOOKUP_Product,2,0)

E3 = HLOOKUP(C3,HLOOKUP_Product,3,0)

E4 = HLOOKUP(C4,HLOOKUP_Product,3,0)

E5 = HLOOKUP(C5,HLOOKUP_Product,3,0)

HLOOKUP_Product: $K$2:$O$4.

Hasil pada tabel isian sebagai berikut:

Anda bisa download contoh-contoh Vlookup dan Hlookup di atas pada link di bawah ini:

Related posts

Leave a Reply

Your email address will not be published. Required fields are marked *