Contoh dan Cara Penulisan Sintaks VLOOKUP dan HLOOKUP di EXCEL


Artikel ini membahas lookup value, table array, dan range lookup yang merupakan argumen dari sintaks untuk VLOOKUP dan HLOOKUP.

Perbedaan penggunaan fungsi VLOOKUP dan HLOOKUP hanya pada susunan tabel. Bila tabel tersusun secara horizontal (kolom), maka kita menggunakan fungsi HLOOKUP. Dan bila tabel tersusun secara vertikal (baris), kita menggunakan fungsi VLOOKUP. Anda bisa melihat contoh tabel di artikel ini: Cara Menggunakan Fungsi VLOOKUP dan HLOOKUP di Excel.

 

Cara Penulisan Sintaks:
  • =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
  • =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

 

Catatan:

Pemisah argumen pada tutorial ini menggunakan tanda titik koma (;). Pemisah argumen juga bisa menggunakan tanda koma (,). Ini tergantung pada pengaturan di komputer.


Contoh:
  • Menggunakan tanda titik koma: =VLOOKUP(B12;$A$3:$B$6;2;FALSE)
  • Menggunakan tanda koma: =VLOOKUP(B12,$A$3:$B$6,2,FALSE)

 

Berikut penjelasan detilnya.

 

Tentang Lookup_value

  1. Lookup_value adalah sel referensi (misalnya sel A1) atau nilai yang dijadikan kunci dalam pencarian data. Nilai bisa berupa teks atau angka.
  2. Lookup_value harus berada pada baris atau kolom pertama tabel penyimpan data.
    Contoh posisi lookup_value pada tabel Excel
  3. Tidak berlaku case sensitive untuk lookup_value. Teks Jan, JaN, atau JAN dianggap sama oleh Excel.
  4. Lookup_value berupa teks harus diberi tanda petik dua ("").
    Contoh: =HLOOKUP("feb";$A$6:$J$7;2;FALSE).
  5. Contoh berikut menggunakan lookup_value sel referensi A10. Keuntungan menggunakan sel referensi adalah apabila isi sel berubah, kita tidak perlu mengubah sintaksnya.

    Contoh: Rate untuk Jan adalah 2,5. Bila kita ingin mendapatkan Rate untuk Feb, isi sel A10 cukup diganti ke Feb dan Rate pada sel B10 akan otomatis berubah menjadi 3 tanpa harus mengedit sintaks.
    Contoh penggunaan lookup_value sel referensi pada HLOOKUP
  6. Lihat di sini: Cara Menggabungkan Fungsi LEFT, RIGHT, dan MID dengan Fungsi VLOOKUP / HLOOKUP, untuk cara mengambil sebagian isi sel sebagai Lookup_value.

 

Tentang Table_array

Table_array adalah tabel atau range yang menyimpan data yang ingin dicari.

Table_array bisa ditulis seperti berikut:
  1. =VLOOKUP(B12;A3:B6;2;FALSE)
    Table_array: A3:B6. Jika formula di-copy paste ke baris berikutnya, hasilnya bisa error. Untuk itu sebaiknya gunakan cara  b dan c.

  2. =VLOOKUP(B12;$A$3:$B$6;2;FALSE)
    Table_array $A$3:$B$6 menggunakan absolute reference (simbol $).

  3. =VLOOKUP(B12;Tabel_Komisi;2;FALSE)
    Table_array menggunakan nama (Tabel_Komisi).
    Cara memberi nama: pilih range (kumpulan sel) dan klik kanan. Pilih Name a Range dan ketik nama tabel di kotak Name. Klik OK.

 

Tentang Range_lookup

  1. Range_lookup adalah nilai logika TRUE atau FALSE, dimana kita ingin fungsi VLOOKUP atau HLOOKUP mengembalikan nilai dengan metode kira-kira (TRUE) atau mengembalikan nilai secara tepat (FALSE).
  2. Range_lookup tidak wajib ditulis. Bila dikosongkan, maka range_lookup menggunakan metode kira-kira (TRUE).
  3. Logika TRUE juga bisa ditulis dengan angka 1 dan FALSE ditulis dengan angka 0 (nol).
  4. Untuk penggunaan logika TRUE, kolom atau baris pertama tabel yang menyimpan data yang dicari (lookup_value) harus diurutkan secara ascending (dari nilai terendah ke nilai tertinggi).

 

Contoh penggunaan range_lookup logika TRUE

Contoh tabel penggunaan range_lookup logika TRUE VLOOKUP
Penjelasan Tabel
  • Kolom Komisi pada Tabel Perhitungan Komisi akan diisi berdasarkan data di Tabel Komisi.Dimana untuk Jumlah Penjualan 1.000.000 - 4.999.999 mendapatkan komisi 3%, 5.000.000 - 9.999.999 mendapatkan komisi 5%, dan seterusnya.
  • Sintaks untuk mengisi sel C13: =VLOOKUP(B13;$A$3:$B$7;2;TRUE)
    • Lookup_value: nilai sel B13, yaitu 3.000.000.
    • Table_array: range $A$3:$B$7 pada Tabel Komisi.
    • Col_index_num: mengambil nilai dari kolom 2 (kolom Komisi - Tabel Komisi).
    • Range_lookup: menggunakan metode kira-kira (TRUE). Dimana bila tidak menemukan lookup_value dengan nilai yang sama (3.000.000), maka cari nilai terdekat yang lebih kecil dari lookup_value. Nilai yang lebih kecil adalah 1.000.000, dengan begitu nilai yang dikembalikan adalah 3%.
  • Selanjutnya formula disalin ke sel berikutnya (C14:C17). Hasilnya adalah seperti tabel berikut ini.
    Contoh tabel hasil penggunaan range_lookup logika TRUE VLOOKUP
  • Metode kira-kira (TRUE) mengharuskan data pada kolom atau baris pertama yang berisi lookup_value pada table_array diurutkan dari nilai terkecil ke nilai terbesar. Bila tidak, akan mendapatkan nilai error seperti contoh berikut.
    Contoh error data tabel tidak diurutkan pada logika TRUE VLOOKUP

 

Contoh penggunaan range_lookup logika FALSE

Contoh tabel penggunaan range_lookup logika FALSE VLOOKUP
Penjelasan Tabel
  • Kolom Harga pada Tabel Penjualan akan diisi berdasarkan data dari Tabel Size.
  • Sintaks untuk mengisi sel C12: =VLOOKUP(B12;$A$3:$B$6;2;FALSE)
    • Lookup_value: nilai sel B12, yaitu M.
    • Table_array: range $A$3:$B$6 pada Tabel Size.
    • Col_index_num: mengambil nilai dari kolom 2 (kolom Harga - Tabel Size).
    • Range_lookup: menggunakan metode pengembalian nilai secara tepat (FALSE). Dimana bila tidak menemukan lookup_value dengan nilai yang sama (M), maka kembalikan nilai error.
  • Selanjutnya formula disalin ke sel berikutnya (C13:C16). Hasilnya adalah seperti tabel berikut ini.
    Contoh tabel hasil penggunaan range_lookup logika FALSE VLOOKUP
  • Tabel berikut merupakan contoh pengembalian nilai error (#N/A), karena XXL tidak terdapat pada Tabel Size. Untuk mencegah terjadi salah ketik, gunakan validasi data seperti pada tutorial ini: Cara Validasi Data yang Diinput pada Lembar Kerja Excel.
    Contoh pegembalian nilai error untuk logika FALSE VLOOKUP

 

Ada saran atau pertanyaan?

Contoh dan penjelasan lain untuk VLOOKUP dan HLOOKUP juga ada di artikel ini: 9 Video Tutorial VLOOKUP dan HLOOKUP.

Bila Anda memiliki tip trik, saran atau pertanyaan untuk topik ini, silakan sampaikan di kotak komentar.

Share this

Related Posts

Previous
Next Post »

45 comments

Tulis Komentar
Anonymous
15 April, 2013 22:45 delete

thanks gan

Reply
Anonymous
01 July, 2013 22:09 delete

Terimakasih, telah membagi ilmu yang bermanfaat.....

Reply
02 November, 2013 20:06 delete

Pa...saya ada case di mana sy punya table yg maw sy kasi fungsi vlookup, masalahnya table tsb tersebar di 30 sheet, tiap sheet punya format table yg sama...sedangkan source tablenya ada di satu sheet pada file yg berbeda...gmn cara paling efektif utk memberi fungsi vlookup pd table di sheet2 tsb?(sbnrx sy bs sj gunakan query di access utk dpt valuenya, tp nanti ttp saja sy hrs mengedit jadi bbrp sheet lg plus memformat ulang sheet2 tsb secara di sheet2 tsb sdh terbentuk format laporan yg baku*pd akhirnya time proses lbh lama dan potensi human error lbh besar pa) Mohon pencerahannya, tks b4.

Reply
02 November, 2013 20:17 delete

Slm kenal pa...saya punya case sbb:
Sy punya table yg hrs diupdate value2nya dgn source data pada table di file excel lain, masalahnya table yg hrs diupdate tsb ada di lebih dari 1 sheet (tepatnya 30 sheet dengan format yg sama), bisakah sy gunakan fungsi vlookup utk ini? Apa sy hrs mengedit tiap sheet?(Gmn cara yg paling efektif?), sbg informasi table pd sheet2 tsb sdh terformat sbg format lap yg baku...mhn pencerahannya pa,tks b4, oya slm kenal.he

Reply
04 November, 2013 15:06 delete

Salam kenal juga,

Maaf, berhubung weekend, saya baru bisa menjawab hari ini.
Ya, Anda bisa menggunakan fungsi VLOOKUP untuk mengambil nilai dari data source.
Dan apakah harus mengedit setiap sheet? Bila isi sel untuk Lookup_value pada 30 sheet tersebut harus berubah, seperti contoh nilai kurs di artikel ini, maka perlu edit semua sheet tersebut.

Bila Anda ingin menggunakan query Access dan menghubungkannya ke Excel, artikel ini mungkin bisa membantu Anda -> Exchange (copy, import, export) data between Excel and Access.

Reply
09 November, 2013 14:42 delete

Terima kasih, tutorialnya

Reply
20 November, 2013 13:45 delete

kalau 2 vlookup bagaimana gan

Reply
20 November, 2013 14:21 delete

@ilham hidayat,

Penulisan 2 vlookup dalam satu formula seperti contoh berikut. Fungsi ditulis berurutan dan disambung dengan tanda koma:
=VLOOKUP(B12,$AA:$AB,2,0)&", "&VLOOKUP(G12,$G:$I,3,0)

Reply
Anonymous
26 November, 2013 08:48 delete

kalo lookup value ny ada huruf dan angka misal A1,
sedangkan yang ditabel ny angkanya aja, gimana caranya y,terimakasih..

Reply
26 November, 2013 09:13 delete

Anda bisa menggunakan sel referensi untuk lookup value. Misalnya, lookup value berada di sel B1, maka rumusnya ditulis seperti berikut: =VLOOKUP(B1;$A$6:$J$7;2;FALSE)

Reply
21 December, 2013 09:00 delete

bos,ada ga caranya,kalo kita move or copy data,terus data yang kita copy bisa ikut berubah sesuai dengan data aslinya??

Reply
21 December, 2013 11:00 delete

Bisa, dengan syarat data menggunakan formula atau fungsi.

Reply
21 February, 2014 09:35 delete

Mas kalau saya menggunakan satu formula vlookup untuk mengambil data dari 2 data range yang berada di 2 sheet yg berbeda bisa tidak ya, nama data range saya DataTOB satunya Data TB, gimana caranya ya mas, minta tolong bantuannya dunk mas?

Reply
21 February, 2014 17:52 delete

misal kan kode pinjaman 01NGA-024,, nah kategori yang mau dibaca NGA nya yaitu NIAGA,, gimana caranya ??

Reply
21 February, 2014 18:13 delete

kalau kodenya itu 01PNP-025, terus cara nyari kategori PNP = penumpang, ini bagaimana ??

Reply
25 February, 2014 12:19 delete

Di artikel ini: Excel VLOOKUP Multiple Sheets, ada penjelasan dan contoh gambar untuk formula vlookup dengan banyak lembar kerja.

Reply
Anonymous
25 February, 2014 22:28 delete

thanks bro !

Reply
27 February, 2014 10:31 delete

Coba gunakan formula: =VLOOKUP(MID(A7;3;3);$A$1:$B$2;2;FALSE)

MID(A7;3;3) untuk mengambil nilai PNP.
A7 adalah sel yang berisi 01PNP-025.
$A$1:$B$2 range data yang berisi sel PNP dan sel Penumpang

Contoh detilnya bisa dilihat di tutorial ini: Cara Menggabungkan Fungsi LEFT, RIGHT, dan MID dengan Fungsi VLOOKUP / HLOOKUP

Reply
21 March, 2014 05:49 delete

Terimakasih Sangat Membantu ! :D
Posting yang sangat berguna :D

Reply
21 March, 2014 12:43 delete

Kenapa harus pake kata false??

Reply
16 April, 2014 19:42 delete

Apakah ada keterangan error-nya?

Reply
Anonymous
16 May, 2014 11:21 delete

Berarti penggunaan range_lookup lebih baik pakai false klo datanya tidak urut klo datanya udah urut berarti pakai true begitu???
jadi penggunaan true dan false tergantung dari datanya itu sendiri?
soalnya saya bingung penggunaan true atau falsenya..
terimakasih

Reply
17 May, 2014 11:05 delete

Penggunaan true dan false tidak tergantung pada datanya, tetapi pada nilai yang ingin didapatkan.
Yaitu apakah kita ingin mengembalikan nilai dengan metode kira-kira (TRUE) atau mengembalikan nilai secara tepat (FALSE).

Reply
09 June, 2014 15:45 delete

Salam Kenal...

Pa saya sudah coba fungsi vlookup untuk kompare data (mencari apakah ada perbedaan harga dari sheet1 dan sheet2), tapi karena data saya mencapai 40rb lebih jadi prosesnya lambat ya ? Apakah ada cara nya mengatasi calculating yang lambat? Terima kasih

Reply
10 June, 2014 08:00 delete

Salam kenal juga,

Sudahkah Anda coba menggunakan gabungan fungsi INDEX MATCH?
(Tutorial 1) | (Tutorial 2)

Reply
10 June, 2014 09:59 delete

Kenapa yang saya coba tetap ga berhasil, data sourcenya horisontal, tapi outputnya mau vertikal gimana nyarinya?

Reply
11 June, 2014 09:06 delete

Gunakan HLOOKUP bila data sourcenya horisontal walaupun outputnya vertikal.

Reply
05 October, 2014 11:56 delete

sis saya mau tanya cara hilangin tanda #n/a bila cell masih kosong dan tanpa ada formula tambahan? , sebelum ya trimakasih y dan saya tunggu balasan ya

Reply
06 October, 2014 11:36 delete

Gunakan fungsi IFERROR seperti ini dalam formula:
=IFERROR(VLOOKUP(G16;$A$3:$B$6;2;FALSE)&"";"")

Note: kode &"";"" untuk mengembalikan nilai kosong bila data tidak ditemukan.

Reply
Anonymous
23 January, 2015 13:15 delete

kalo data yang diambil dalam satu kolom yang sama, bagaimana fungsinya?
contoh :
Penjual (kolom A)
A
B
A
C
A
C

Sales (Kolom B)
Cabe
Tomat
Bawang Merah
Bawang Putih
Lada
Kunyit

Penjual A = Cabe, Bawang Merah, Lada

Reply
yuuki
12 March, 2015 00:06 delete

saya coba persis yg komisi itu, tetapi yg hasil komisi kenapa jadi
0.12
0.12
0.07
0.12
0.12
kenapa? saya jadiin persen, di kolom b13 yg 3juta malah dapet komisi 12%
dan yang di kolom b15 yg 14juta malah dapet 7% sendiri...
ini beda apanya? saya udah niru persis caranya.

terimakasih

Reply
12 March, 2015 15:30 delete

Mungkin Anda bisa cek sekali lagi rumusnya =VLOOKUP(B13;$A$3:$B$7;2;TRUE). Apakah lookup value-nya sudah benar B13? begitu juga dengan rangenya $A$3:$B$7.

Untuk kolom B15 yang 14 juta memang tepat dapat 7% karena metode TRUE (metode kira-kira) akan mengembalikan nilai terdekat yang lebih kecil.

Reply
26 May, 2015 09:05 delete

TOLONG MIN...
mencari harga satuan yang di dapat dari jenis dan ukuran...
tabel bantu cuma 1 min...
yang jenis vertical dan ukuran horizontal...
pencerahannya min...

Reply
Anonymous
14 June, 2015 10:46 delete

Tolong min, misalnya.
Tabel.
NILAI GRADE
90-100 A+
80-89 B


Nilai Grade
92 ...
84 ...
93 ...

Cara menentukan Grade tersebut dengan fungsi vlookup gmana min?

Reply
17 June, 2015 19:12 delete

Pertama-tama, Tabel Nilai Grade harus disusun dari angka terkecil ke angka terbesar seperti di bawah ini dan tidak perlu membuat range angka 90-100.
80 B
90 A

Selanjutnya gunakan rumus ini: =VLOOKUP(A5;$A$1:$B$2;2;TRUE)
Ganti A5 dengan alamat sel yang ingin diisi dengan Grade dan $A$1:$B$2 untuk tabel nilai grade Anda.

Reply
Anonymous
26 June, 2015 12:54 delete

ian : Tulisannya edukatif sekali. Andaikan saja tulisannya bisa di download sebagai PDF file.

Reply
Anonymous
21 July, 2015 10:15 delete

aku ga ngerti nih pa

Reply
Anonymous
08 August, 2015 14:15 delete

apakah bisa menggunakan rumus vlookup jika table array dan col_index_num merupakan hasil dari rumus formula IF dan AND?
Saya ingin mencoba membuat rumus menggunakan logika seperti ini : Jika B2 (sel kosong yang merupakan sel kunci) terisi, maka dengan menggunakan rumus Vlookup akan menghasilkan dr kolom no 4(yang berisi hasil dari penggabungan rumus Nested IF dan AND

Reply
10 August, 2015 17:56 delete

Petunjuk di sini mungkin bisa membantu Anda.

Reply
Anonymous
01 December, 2015 08:44 delete

Misal; pada cell A1 dengan angka 704312 (format number) sedangkan pada data range nya yang akan saya vlookup dengan angka yang sama pada cell F1 yaitu 704312 (tetapi merupakan gabungan dari dua data pada cell B2&D2 dengan menggunakan fungsi AND). Jadi ketika saya vlookup, selalunya muncul #N/A. Tetapi ketika angka pada F1 saya copy dan di convert ke format number, fungsi vlookup nya bisa dieksekusi.
Bagaimana caranya agar vlookup nya agar tetap berfungsi tanpa harus saya copy dan convert lagi karena akan memakan waktu dan merusak susunan format table saya?
Terima kasih

Reply
Anonymous
29 May, 2016 21:56 delete

Mau tanya nih aku ada problem tapi jadi bingung sendiri jelasinnya, saya punya data master berisi tgl jam datang jam pulang dll, nah saya ingin melakukan rekap atas semuanya sehingga mudah untuk dibaca pada sheet yang berbeda, dengan rumus pada kolom datang(in)tgl x1 =IFNA(VLOOKUP($D15;Finger!$C$3:$AG$5000;8;FALSE);IFNA(VLOOKUP($D15;Finger!$C$3:$AG$5000;4;FALSE);"")) nah pada saat dia dipindah ke kolom datang (in) tgl x2 yang terbaca ada sama hasilnya dengan rumus =IFNA(VLOOKUP($D15;Finger!$C$3:$AG$5000;8;FALSE);IFNA(VLOOKUP($D15;Finger!$C$3:$AG$5000;4;FALSE);"")).
Apabila diberi tambahan =IFNA(VLOOKUP($D15;Finger!$C$3:$AG$5000;8;FALSE)=$M$12;IFNA(VLOOKUP($D15;Finger!$C$3:$AG$5000;4;FALSE);"")) maka yang keluar adalah "FALSE" mohon bantuannya, bila butuh filenya bisa ke arisr26@gmail.com.
thx

Reply