Download - Fungsi Vlookup dan Hlookup pada Excel
Fungsi VLOOKUP dan HLOOKUP dalam Microsoft Excel berguna untuk membaca suatu tabel, lalu mengambil nilai yang diinginkan pada tabel tersebut berdasarkan kunci tertentu.
Jika tabel tersusun secara vertikal, kita menggunakan fungsi VLOOKUP.
Dan, jika tabel tersusun secara horizontal, maka kita menggunakan fungsi HLOOKUP.
Cara Penulisan:
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)=HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
Dimana:
lookup_value: nilai atau sel referensi yang
dijadikan kunci dalam pencarian data.
table_array: tabel atau range yang menyimpan
data yang ingin dicari.
col_index_num: nomor kolom yang ingin diambil
nilainya untuk fungsi VLOOKUP.
row_index_num: nomor baris yang ingin diambil
nilainya untuk fungsi HLOOKUP.
range_lookup: Nilai logika TRUE atau FALSE,
dimana Anda ingin fungsi VLOOKUP atau HLOOKUP
mengembalikan nilai dengan metode kira-kira
(TRUE) atau mengembalikan nilai secara tepat
(FALSE). Lebih detilnya dapat dibaca di sini.
Contoh VLOOKUP:
=VLOOKUP(1002,$A$2:$C$4,3,FALSE) akan menghasilkan 68
=VLOOKUP(1003,$A$2:$C$4,2,FALSE) akan menghasilkan GHI
=B10*VLOOKUP(C10,$A$2:$C$4,3,FALSE) akan menghasilkan 340
=B11*VLOOKUP(C11,$A$2:$C$4,3,FALSE) akan menghasilkan 320
=B12*VLOOKUP(C12,$A$2:$C$4,3,FALSE) akan menghasilkan 544
Contoh HLOOKUP:
=HLOOKUP(B1,$B$1:$D$3,2,FALSE) akan menghasilkan XYZ
=HLOOKUP(B1,$B$1:$D$3,3,FALSE) akan menghasilkan 33
Sumber lain tentang cara penggunaan VLOOKUP dan HLOOKUP dapat diperoleh di sini:
Vlookup week - Chandoo.orgBlog ini memiliki kumpulan artikel tentang penggunaan VLOOKUP yang lebih kompleks dan juga tersedia lembar petunjuk VLOOKUP untuk
di-download.
Microsoft SupportMemiliki kumpulan artikel tentang VLOOKUP dan HLOOKUP, seperti cara menangani error pada penggunaan fungsi VLOOKUP dan HLOOKUP.
• fungsi flookupFungsi vlookup merupakan fungsi bantuan references. Fungsi Vlookup dipakai untuk menghasilkan nilai pada tabel secara vertikal.Penulisan :=VLOOKUP(nama_baris;tabel;kolom_pencarian;range_lookup)misal :
solusi :Pada A8 masukkan nilai NIM terlebih dahulu yang terdapat pada tabel NIM-Nama. kemudian pada B8 ketikkan formula =LOOKUP(A8;$A$2:$B$5;2;0).• fungsi hlookupFungsi Hlookup merupakan fungsi bantuan references juga. bedanya Fungsi Hlookup dipakai untuk menghasilkan nilai pada tabel secara horizontal.Penulisan :=HLOOKUP(nama_kolom;tabel;baris_pencarian;range_lookup)
misal :
solusi :Pada C6 ketikkan formula =HLOOKUP(B6;$B$1:$F$3;2;0).• lookup valuePada prinsipnya sama dengan Vlookup, namun pada lookup value ini memungkinkan kita untuk mengambil beberapa data dari tabel lain sabagai referensi / patokan.misal :Dari 2 tabel yakni tabel peminjaman dan tabel buku akan dibuat Daftar Peminjaman Buku.
solusi :– Nama pada cell B11 adl =VLOOKUP(A11;$A$2:$D$5;2;0)– Perihal pada cell D11 adl =
VLOOKUP(C11;$F$2:$H$5;2;0)– Judul pada cell E11 adl = VLOOKUP(C11;$F$2:$H$5;3;0)
Vlookup
Fungsi vlookup merupakan sebuah fungsi Ms. Excel yang kurang lebih berfungsi untuk
mencari atau melihat sebuah data “look” dari suatu table secara vertical. Lihat contoh
gambar table dibawah ini
Jika terdapat daftar penjualan barang dalam sebulan dengan menggunakan kode kode
diatas, dan kita harus mendapatkan total pemasukan dari penjualannya, hal termudah
adalah menggunakan fungsi Vlookup adapun syntaknya sebagai berikut
=VLOOKUP(lookup_value,table_array,column_index_number,[range_lookup])
Penyelesaian
Terlihat dalam gambar fungsi vlookup sebagai berikut =VLOOKUP(G2,$B$2:$E$7,3,0)
• G2 ; merupakan letak cell kode yang akan dicari (lookup value)
• $B$2:$E$7 ; merupakan lokasi ataupun daerah cell table keterangan dari kode tersebut
(Table array), perhatikan tanda $, tanda tersebut merupakan pengunci cell agar saat
formula di copy cell tidak bergeser
• 3 ; merupakan nomor kolom yang akan ditampilkan (column index number), kolom 3
merupakan kolom harga, kolom 2 yaitu kolom jenis, kolom 4 adalah kolom kualitas,
sedangkan kolom pertama yaitu kode itu sendiri
• 0 ; merupakan range lookup, dalam point ini range lookup dapat anda isikan true
“benar” kode dalam table urut atau sesuai abjad, jika tidak hasil lookup akan terdapat
#N/A atau tidak sesuai, jika range lookup anda isikan false “salah” hasilnya akan sesuai
meskipun table dalam kondisi tidak urut, untuk angka 0 dapat diartikan “false”
Satu contoh kasus yang lebih kompleks, jika terdapat sebuah kode, dimana kode
tersebut merupakan gabungan beberapa data yang sengaja disingkat agar lebih simple,
Misalkan terdapat sebuah kode di pabrik konveksi, kita disuruh menentukan harga
penjualan sebuah kain berdasarkan waktu produksi, kualitas, dan warna kain
0610AR, “0610” merupakan bulan dan tahun produksi, “A” kualitas kain, dan “H”
warna kainDapat dipastikan kita akan memiliki beberapa tabel harga ataupun potongan menurut1. Waktu produksi kain2. Kualitas kain3. Warna KainUntuk penyelesaian menggunakan fungsi vlookup dalam Ms. Excel sebagai berikut
• Pisahkan kode tersebut menjadi tiga kode dalam cell yang berbeda menggunakan
fungsi Right, Mid dan Left, Untuk penjelasan ketiga fungsi tersebut baca posting
tentang “Fungsi Left, Mid dan Right pada Ms. Excel”
• Jika bentuk kode Tanggal pada table sebagai berikut,
Maka kode yang kita ambil hanya pada bulannya saja “0610” menjadi “6” dengan cara mengganti formula Left menjadi seperti gambar dibawah,
Value adalah converter dari text menjadi angka *Penjelasan Fungsi Value dapat anda baca pada “Fungsi Value pada Ms. Excel”
• Langkah berikutnya adalah menamai ketiga table tersebut pada “name box”, Kegunaan
menamai table tersebut agar dalam penulisan formula kita tidak susah payah untuk blog
“table_array” atau lokasi table, untuk lebih detailnya lihat gambar
Lakukan seperti hal tersebut pada table kualitas dan harga dengan nama “name box” yang berbeda.• Cari hasil lookup dari ketiga kode tersebut sehingga mendapatkan hasil table sesuai kode
Hasil daru formula diatas adalah 0.12 kenapa bukan 12%? Saya katakana sama saja,
12% adalah persentase dari pecahan 12/100 yang merupakan decimal dari angka 0.12
“mungkin anda sudah mengerti karena di SD kelas 3 sudah di bahas dalam satu
semester”, lakukan pada kedua kode selanjutnya
Coba ubah Kode pada cell B3, contoh dengan kode 1110CB atau yang lainnya, cek hasil
lookup dengan table apakah sesuai atau tidak, jika tidak sesuai maka akan tertulis #N/A ,
untuk menghindarinya dapat anda gunakan fungsi“Iferror”,
• Langkah selanjutnya tinggal mencari harga dari kode tersebut yaitu sebagai berikut,
Didalam pelajaran sekolah mungkin kita telah mempelajari matematika dengan bab rugi
laba ataupun bunga dan potongan harga, jadi dari contoh soal diatas dapat kita ambiil
syntak harga sebagai berikut :
Ha = Hs – { Hs x ( Pk + Pp )}Ha : Harga setelah diskonHs : Harga sebelum diskonPk : Diskon menurut Kualitas barangPp : Diskon menurut Bulan Produksi
Dalam penghitungan di Ms. Excel dapat dituliskan sebagai berikut
Jika anda sudah biasa menggunakan Excel, mungkin formula ini lebih cocok untuk anda, yaitu formula dimana pembahasan panjang lebar diatas hanya disingkat kedalam satu
formula saja.
Hlookup
Hlookup adalah sebuah fungsi yang sama dengan vlookup hanya saja terdapat
perbedaan pada lookup data dimana vlookup secara vertical sedangkan hlookup secara
horizontal, untuk lebih jelasnya saya sertakan sampel agar lebih dapat dipahami. Dalam
kasus soal diatas pada penghitungan vlookup terdapat hasil harga berdasarkan kode
yang tersedia, jika terdapat table potongan harga menurut banyaknya penjualan dimana
table tersebut disajikan secara horizontal seperti gambar dibawah ini,
• Pertanyaan :
Pada gambar diatas terllihat table dimana pada kilogram hanya terdapat angka kelipatan
10, bagaimana jika pembelian pada angka 45? Bagaimana penghitungan dalam formula
excel untuk mendapatkan persentase pada angka tersebut jika pada lookup value tidak
ada angka 45?
• Jawaban :
Kita gunakan pembulatan kebawah, 45 kita jadikan 40, 56 jadikan 50, 99 jadikan 90
walaupun angka tersebut dekat dengan angka 100. Adapun caranya menggunakan
fungsi “INT” untuk penjelasan fungsi tersebut silahkan cari di kategori Excel pada blog ini
Int merupakan fungsi Excel yang berguna untuk membuat pecahan ataupun decimal ke
dalam bilangan bulat dengan cara pembulatan kebawah, /10 saya gunakan agar 45
menjadi bilangan decimal yaitu 4,5 setelah itu akan di bulatkan menjadi 4 oleh fungsi
INTdan akhirnya saya kalikan 10 kembali sehingga menjadi angka 40, tapi itu tak penting
yang penting anda tau maksud dari penggunaan fungsi hlookup yang akan saya bahas di
bawah ini
Penggunaan Hlookup pada kasus di atas agar mendapatkan potongan harga sebagai berikut,
• $C$4:$M$5 = Adalah table_array atau lokasi table yang sebenarnya penulisannya
sebagai berikut C4:M5, akan tetapi terdapat tanda dolar $, tanda tersebut berfungsi untuk
mengunci cell, untuk lebih jelasnya lihat “Penggunaan fungsi Ms. Excel”, sebenarnya
anda dapat menggunakan cara seperti pembahasan Vlookup yaitu menamai table pada
name box, tetapi akan lebih baiik jika anda memiliki pengetahuan lebih
• Lihat lingkaran merah di “row_index_num” berbeda pada formula Vlookup dimana
tertulis “col_index_num”, disitulah perbedaannya jika penyajian table secara vertical
ataupun kolom (column) maka kita menggunakan fungsi vlookup, jika penyajian table
secara horizontal, baris atau sering disebut (row) maka kita menggunakan fungsi Hlookup
=HLOOKUP(lookup_value,table_array,row_index_number,[range_lookup])
• Lihat lingkaran kedua, #N/A berarti dalam table tidak ada angka tersebut, bukannya 230
merupakan >100? Benar, akan tetapi dalam table tidak terdapat angka 230 hanya >100.
Untuk mengatasi hal tersebut maka kita perlu menggunakan fungsi “If” atau “Iferror”Penggunaan if
“Hlookup(“>100”,$C$4:$M$5,2,0) dapat langsung anda ganti “0.5” yang merupakan hasil dari fungsi tersebutPenggunaan iferror
Untuk penjelasan fungsi IF dan IFERROR silahkan lihat pada kategori Excel di blog ini
Setelah saya perhatikan ternyata ada beberapa kesalahan seperti kolom potongan saya
isi dengan hasil pembulatan kebawah dan kolom pembayaran terisi oleh potongan harga,
nah dibawah ini gambar secara lengkap yang telah direvisi serta pemadatan formula agar
simple.
Dan akhirnya selesailah pembahasan tentang fungsi Hlookup dan Vlookup pada Ms.
Excel, Jika ada pertanyaan dengan senang hati saya akan menjawabnya jika didalam
penjelasan ini terdapan penulisan yang kurang jelas, dan jika ada masukan, saran
ataupun kritik dengan lapang dada saya akan menerimanya dan memebenahinya.
Semoga berguna
Average
Syntak fungsi Average
=AVERAGE(number1;[number2];... ) Berfungsi untuk mencari rata rata dari beberapa bilangan
Averagea
Averagea adalah sebuah fungsi yang sama dengan fungsi
Average hanya saja terdapat perbedaan pada jumlah data yang
akan dirata rata, pada average apabila terdapat kesalahan
penulisan data maka data tersebut akan di anggap tidak ada,
berbeda dengan Averagea jika terjadi kesalahan pada
penulisan data maka hal tersebut tidak berpengaruh terhadap
jumlah data.
Dapat kita lihat pada gambar diatas hasil Average adalah 4,
diperoleh dari (1+3+8)/3 dimana jumlah data menjadi 3 karena
kesalahan penulisan data sedangkan pada Averagea hasilnya
adalah 3, diperoleh dari (0+1+3+8)/4dimana kesalahan
penulisan data tidak mempengaruhi jumlah data, jadi data tetap
dianggap 4.