Fungsi VLOOKUP dan HLOOKUP Excel, Lengkap dengan Contoh dan Cara Menggunakannya

Jika Anda sudah terbiasa bekerja dengan jumlah data yang cukup besar di Excel, maka biasanya data-data tersebut akan dibagikan ke dalam beberapa sheet terpisah. Salah satu tujuannya agar data tetap dapat terorganisasi dengan baik, sehingga mudah jika dicari kembali.

Pada Excel, biasanya data-data antar sheet saling berelasi, misalkan ada dua sheet yaitu sheet A_ dan sheet B_. Bagaimana cara menghubungkan data dari berbagai sheet tersebut jika diperlukan?

Salah satu caranya adalah dengan menggunakan fungsi/rumus Lookup, yaitu fungsi yang melakukan pencarian nilai padanan dari suatu sel ke sel yang terdapat pada tabel lain, baik sheet yang sama maupun terpisah.

Fungsi lookup pada Excel ada dua, yaitu:

• Fungsi VLOOKUP (Vertical Lookup)
• Fungsi HLOOKUP (Horizontal Lookup)

Fungsi VLOOKUP Excel

Apa itu Fungsi VLOOKUP??

VLOOKUP adalah fungsi excel yang melakukan pencarian dari suatu nilai pada kolom pertama dari suatu tabel array, dan jika ditemukan akan mengembalikan nilai lainnya pada baris yang sama dari hasil pencarian.

Rumus Fungsi VLOOKUP

Berikut adalah syntax dan keterangan dari fungsi VLOOKUP

Rumus VLOOKUP

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

Keterangan:

• lookup_value : nilai yang akan dicari ke table_array.
  Ini dapat berupa nilai (nomor, tanggal atau teks) atau referensi sel (referensi ke sel yang berisi nilai lookup), atau nilai yang dikembalikan oleh beberapa fungsi Excel lainnya. Sebagai contoh, rumus: =VLOOKUP (40;A2:B11;2;FALSE) akan mencari nilai 40.


• table_array : range nilai dimana terdapat nilai yang dicari.
  Ingat, fungsi VLOOKUP selalu mencari nilai lookup di kolom pertama table_array. Table_array Anda mungkin berisi berbagai nilai seperti teks, tanggal, angka, atau nilai-nilai logis. Nilai di sini bersifat sensitif, yang berarti bahwa teks huruf besar dan huruf kecil harus diperhatikan sebagai nilai identik.
  
  Jadi, contoh rumus yang kita gunakan disini adalah:
  =VLOOKUP (40;A2:B11;2;FALSE)
  artinya akan mencari nilai “40″ dalam sel A2 sampai A11.


• col_index_num : indeks kolom yang di cari.
  Nomor kolom di table_array di mana nilai dalam baris yang sesuai harus dikembalikan. Sedangkan tabel yang paling kiri dari table_array ditentukan dengan 1, kolom kedua adalah 2, kolom ketiga adalah 3, dan seterusnya.
  
  Contoh:
  =VLOOKUP (40;A2:B11;2;FALSE)
  artinya akan mencari nilai “40″ dalam sel A2 sampai B11 dan mengembalikan nilai yang cocok dari kolom B (karena B adalah kolom ke-2 dalam table_array A2:B11).


• range_lookup (FALSE/TRUE atau 0/1)
  menentukan apakah Anda sedang mencari nilai yang sama (FALSE) atau perkiraan pencocokan (TRUE atau diabaikan). Parameter akhir ini adalah opsional, tetapi sangat penting.
  Contoh:
  
  

Yang Harus di Ingat dalam Penggunaan Excel VLOOKUP

• Fungsi Excel VLOOKUP tidak dapat membaca dari kiri, namun fungsi ini selalu mencari nilai lookup di kolom paling kiri dari rentang lookup (table_array).
• Dalam rumus VLOOKUP, semua nilai adalah nilai sensitif, yang berarti bahwa karakter huruf besar dan huruf kecil diperhatikan secara detail.
• Jika nilai lookup lebih kecil dari nilai terkecil di kolom pertama dari jangkauan pencarian Anda, maka fungsi VLOOKUP akan menghasilkan hasil #N/A error.
• Jika parameter ke tiga (col_index_num) kurang dari 1, rumus VLOOKUP akan menampilkan hasil #VALUE! eror. Sebaliknya, jika dalam pengerjaan bahwa ini lebih besar dari jumlah kolom dalam rentang pencarian Anda (table_array), rumus akan menampilkan #REF! error.
• Gunakan referensi sel absolut dalam rumus table_array VLOOKUP Anda untuk mendapatkan rentang lookup yang tepat ketika menghadapi rumus. Pertimbangkan untuk menggunakan rentang lookup dengan nama atau tabel Excel sebagai alternatif.
• Saat mencari dengan menggunakan metode pencocokan perkiraan (range_lookup set ke TRUE atau diabaikan), selalu miliki data pada kolom pertama dalam rentang pencarian yang diurutkan dalam urutan yang menarik.
• Dan terakhir, ingat tentang pentingnya parameter akhir. Penyediaan TRUE atau FALSE di saat yang tepat akan menghindarkan Anda dari banyak kebingungan.

Contoh Fungsi VLOOKUP

Buatlah tabel berikut di sheet1 dan sheet2.

Gunakan fungsi VLookup untuk menghubungkan data Sheet1 dan di Sheet2, sehingga kolom nama barang dan harga di sheet1 akan tampil di sheet2 berdasarkan Kode barang.

Langkah-langkahnya sebagai berikut:

Langkah 1: Aktifkan Sheet2

Langkah 2: Pilih sel B3

Langkah 3: Tulis rumus =VLOOKUP(A3:A6;Sheet1!A2:C5;2;FALSE)

Langkah 4: Agar nilai lookup_value dan table_array absolut, tekan F4 setelah memblok range lookup_value dan table_array.

Sehingga rumus diatas menjadi:
=VLOOKUP($A$3:$A$6;Sheet1!$A$2:$C$5;2;FALSE)

Langkah 5: Kemudian tekan Enter, copy lalu paste rumus ini ke sel berikutnya. Meskipun rumus sel B3, B4, B5 dan B6 sama namun hasilnya berbeda sesuai dengan data yang ada di Sheet1

Langkah 6: Sekarang aktifkan sel C3

Langkah 7: Tulis rumus =VLOOKUP(A3:A6;Sheet1!A2:C5;3;FALSE)

Langkah 8: Ubah nilai lookup_value dan table_array dengan menekan F4 dikeyboard. Rumus diatas menjadi =VLOOKUP($A$3:$A$6;Sheet1!$A$2:$C$5;3;FALSE)

Langkah 9: Lanjutkan dengan menyalin lalu memastekan rumus ini ke sel berikutnya.

Langkah 10: Bila rumus yang anda tulis sudah benar maka hasil akhirnya seperti gambar berikut:

Langkah 11: Selesai

Fungsi HLOOKUP Excel

Apa itu Fungsi HLOOKUP?

Fungsi HLOOKUP adalah fungsi excel yang melakukan pencarian horizontal dengan mencari nilai di baris atas sebuah tabel dan mengembalikan nilai dalam kolom yang sama berdasarkan index_number.

Rumus Fungsi HLOOKUP

Berikut adalah syntax dan keterangan dari fungsi HLOOKUP

Rumus VLOOKUP

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

Keterangan:

• lookup_value : nilai yang akan dicari ke table_array
• table_array : tabel mana yang nilainya sedang dicari
• row_index_num : indeks baris yang di cari
• range_lookup (FALSE/TRUE)
  menentukan apakah Anda sedang mencari nilai yang sama (FALSE) atau perkiraan pencocokan (TRUE atau diabaikan). Parameter akhir ini adalah opsional, tetapi sangat penting.

Contoh Fungsi HLOOKUP

Buatlah tabel seperti gambar berikut

Langkah-langkah membuat rumus HLOOKUP:

Langkah 1: Pilih sel C3

Langkah 2: Untuk menulis rumus Hlookup sebenarnya sama saja dengan langkah menulis rumus Vlookup sebelumnya. Untuk menambah wawasan teman-teman, kita juga bisa menulis rumus Vlookup maupun Hlookup menggunakan kotak dialog yang tersedia pada excel.

Begini caranya. Pada menu FORMULAS klik Lookup & Reference pada tab Function Library kemudian klik HLOOKUP

Langkah 3: Akan muncul tabel dialog berikut:

Langkah 4: Isilah tabel dialog sesuai dengan data yang tertera pada gambar berikut, kemudian klik OK.

Langkah 5: Hasilnya sebagai berikut:

Pada gambar di atas, tampak bahwa sel C3 menghasilkan hasil #N/A error. Kenapa demikian???

Sampai langkah ini masih normal sebenarnya, jangan khawatir meskipun nilai yang tertera masih memunculkan nilai error, itu tidak apa-apa kok.

Nilai error ini akan berubah otomatis setelah kita memasukkan nama item di sel C3. Sekedar info, teman-teman juga bisa mengenali berbagai pesan error yang ada di excel dengan membaca artikel 7 pesan error excel.

Mari kita lanjut!!!

Langkah 6: Sekarang pilih sel C2

Agar lebih mudah maka nama item di sel C2 kita munculkan dalam bentuk menu yang bisa kita pilih sesuka hati kita. Jadi kita tidak perlu mengetik satu persatu nama itemnya. Caranya adalah dengan membuat daftar dropdown yang berisi daftar nama-nama item.

Langkah 7: Pilih Data Validation dari menu Data seperti gambar berikut.

Langkah 8: Pada Allow pilih List

Langkah 9: Untuk menu Source, isikan rumus =C$5:I$5

Langkah 10: Lebih jelasnya, lihat gambar berikut, terakhir klik OK.

Langkah 11: Hasilnya sebagai berikut:

Dari daftar dropdown, silahkan pilih salah satu nama item yang tersedia. Nama item yang dipilih akan dijadikan sebagai lookup_value di C2. Sehingga dengan demikian harga di C3 akan terisi otomatis dengan menyesuaikan harga item pada tabel HLOOKUP yang tersedia.

Langkah 12: Selesai

Bagaimana teman-teman?? Mudah atau tidak?? Kalau masih ada kendala mari kita diskusikan melalui kolom komentar dibawah.

Itu saja dulu artikel tentang fungsi VLOOKUP dan fungsi HLOOKUP excel yang turut dilengkapi dengan contoh beserta cara menggunakannya, semoga bermanfaat buat teman-teman sekalian.