Rumus Excel VLOOKUP dan HLOOKUP adalah rumus yang paling sering digunakan membuat lookup function untuk melihat data secara vertikal dan horizontal. Berbeda dengan rumus LOOKUP yang dapat mencocokkan data teks maupun numerik, sedangkan rumus VLOOKUP dan HLOOKUP khusus untuk mencocokkan data numerik. Penggunaan rumus VLOOKUP dan HLOOKUP merupakan kompleksitas numerik terhadap rumus LOOKUP yang sederhana. Berikut cara menggunakan VLOOKUP dan HLOOKUP beserta contohnya.
Navigasi Cepat:
- Cara VLOOKUP pada Microsoft Excel
- Cara HLOOKUP pada Microsoft Excel
B. Cara VLOOKUP pada Microsoft Excel
Rumus Excel VLOOKUP adalah rumus yang digunakan melihat data secara vertikal (data diurutkan secara vertikal), dengan mengambil nilai dari suatu table_array dimana data range referensi terletak pada kolom pertama.
Cara Menggunakan VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])- lookup_value adalah sel yang akan dicocokkan dengan table_array.
- table_array adalah tabel array yang menjadi referensi lookup_value serta data sel yang dilihat. Kolom pertama akan menjadi sel referensi (pencocokan). Dapat diisi dengan nama range atau referensi langsung.
- col_index_num adalah urutan kolom yang akan dilihat atau ditampilkan hasilnya.
- [range_lookup] adalah argument opsional (bisa diisi/tidak) yang dapat didefinisikan sebagai boolean TRUE atau FALSE.
- TRUE berarti fungsi melakukan pencocokan data berdasarkan aproksimasi matematika (approximate match). Secara default fungsi bernilai TRUE. Untuk bekerja, kolom pertama (referensi) table_array harus berisi data numerik yang diurutkan dari kecil ke besar.
- FALSE berarti fungsi melakukan pencocokan data secara tepat (exact match). Jika data yang diuji tidak ada yang cocok, maka hasil yang ditampilkan #N/A.
PENTING: Penggunaan koma (,) atau semicolon (;) dalam FORMULA disesuaikan dengan versi Microsoft Excel dan konfigurasi bahasa pada komputer anda.
Contoh:
Diketahui pada tabel pertama, merupakan table_array yang berisi peraturan suatu perusahaan kopi kepada salesnya dengan range referensi yang telah urut dari kecil ke besar. Akan dihitung bonus penjualan dan penghargaan yang didapatkan sales-salesnya dalam sebulan pada tabel kedua adalah sebagai berikut.
Berikut langkah-langkah yang perlu dilakukan
- Opsional: Membuat nama range dari table_array A3:C9, misalnya dengan nama "sales". Hal ini dapat mempermudah saat data yang diproses berjumlah banyak.
- Membuat formula pada penjual pertama
- Untuk bonus digunakan index kolom 2 =VLOOKUP(B12,sales,2,TRUE)
Jika tidak mendefinisikan nama range, gunakan:
=VLOOKUP(B12,A3:C9,2,TRUE) - Untuk penghargaan digunakan indek kolom 3 =VLOOKUP(B12,sales,3,FALSE)
Jika tidak mendefinisikan nama range, gunakan:
=VLOOKUP(B12,A3:C9,3,FALSE)
- Untuk bonus digunakan index kolom 2 =VLOOKUP(B12,sales,2,TRUE)
- Melakukan Autofill
Terlihat:
- Kolom bonus menampilkan data berdasarkan aproksimasi (TRUE) pencocokan penjualan sales, dimana Wikan (1200 ≥ 1000 ), Julia (1000 = 1000), Nary (300 ≥ 200), Sani (490 ≥ 400) dan Ade (88 ≥ 50). Sehingga dapat diketahui interval aproksimasi setiap sel referensi adalah lebih dari nilainya dan kurang dari nilai sel referensi berikut.
- Kolom Penghargaan menampilkan data dengan pencocokan data sama (FALSE) dengan sel referensi, sehingga Wikan, Nary, Sani dan Ade menampilkan #N/A sedangkan karena hanya penjualan Julia yang identik dengan referensi table_array (nilai 1000 ada dalam tabel referensi) menampilkan data dari table_array.
Baca juga: Cara Menggunakan Rumus VLOOKUP Antar Sheet
C. Cara HLOOKUP pada Microsoft Excel
HLOOKUP adalah rumus yang digunakan melihat data secara horizontal (data diurutkan secara horizontal), dengan mengambil nilai dari suatu table_array dimana data range referensi terletak pada baris pertama.
Cara Menggunakan HLOOKUP
=HLOOKUP(lookup_value, table_array, row_index_num,[range_lookup])- lookup_value adalah sel yang akan dicocokkan dengan table_array.
- table_array adalah tabel array yang menjadi referensi lookup_value serta data sel yang dilihat. Baris pertama akan menjadi sel referensi (pencocokan). Dapat diisi dengan nama range atau referensi langsung.
- row_index_num adalah urutan baris yang akan dilihat atau ditampilkan hasilnya.
- [range_lookup] adalah argument opsional (bisa diisi/tidak) yang dapat didefinisikan sebagai boolean TRUE atau FALSE.
- TRUE berarti fungsi melakukan pencocokan data berdasarkan aproksimasi matematika (approximate match). Secara default fungsi bernilai TRUE. Untuk bekerja, baris pertama (referensi) table_array harus berisi data numerik yang diurutkan dari kecil ke besar.
- FALSE berarti fungsi melakukan pencocokan data secara tepat (exact match). Jika data yang diuji tidak ada yang cocok, maka hasil yang ditampilkan #N/A.
PENTING: Penggunaan koma (,) atau semicolon (;) dalam FORMULA disesuaikan dengan versi Microsoft Excel dan konfigurasi bahasa pada komputer anda.
Contoh:
Seorang dosen membuat jadwal ujian praktikum pemrograman komputer pada tabel pertama. Karena praktikum tersebut membutuhkan waktu yang lama. Sehingga dosen tersebut membuat jadwal khusus pada seluruh mahasiswa berdasarkan kebiasaan bangun pagi. Untuk mempercepat pekerjaan, dosen tersebut menggunakan HLOOKUP untuk membuat jadwal khusus itu adalah sebagai berikut.
Berikut langkah-langkah yang perlu dilakukan:
Baca juga: 3 Cara Memberi Nama Range dan Menggunakan Name Manager Excel
- Opsional: Membuat nama range dari table_array B2:E4, misalnya dengan nama "ujian". Hal ini dapat mempermudah saat data yang diproses berjumlah banyak.
- Membuat formula pada mahasiswa pertama
- Untuk pukul digunakan index baris 2 =HLOOKUP(B7,ujian,2,FALSE)
Jika tidak mendefinisikan nama range, gunakan:
=HLOOKUP(B7,B2:E4,2,FALSE) - Untuk ruangan digunakan indek baris 3 =HLOOKUP(B7,ujian,3,FALSE)
Jika tidak mendefinisikan nama range, gunakan:
=HLOOKUP(B7,B2:E4,3,FALSE)
- Untuk pukul digunakan index baris 2 =HLOOKUP(B7,ujian,2,FALSE)
- Melakukan Autofill
Catatan: Tipe data waktu perlu menggunakan argumen FALSE, karena aproksimasi waktu menggunakan rumus standar VLOOKUP dan HLOOKUP tidak dapat bekerja.
Anda juga dapat melakukan aproksimasi seperti contoh VLOOKUP menggunakan HLOOKUP, yang membedakan hanya orientasi table_array.
Baca juga tutorial lainnya: Daftar Isi Tutorial Excel
Sekian artikel Pengertian Rumus Excel VLOOKUP dan HLOOKUP Beserta Contohnya. Nantikan artikel menarik lainnya dan mohon kesediaannya untuk share dan juga menyukai Fans Page Advernesia. Terima kasih…
Excel untuk Microsoft 365 Excel untuk Microsoft 365 untuk Mac Excel untuk web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 untuk Mac Excel 2016 Excel 2016 untuk Mac Excel 2013 Excel 2010 Excel 2007 Excel untuk Mac 2011 Excel Starter 2010 Lainnya...Kurang
Tips: Coba gunakan fungsi XLOOKUP baru, versi VLOOKUP yang ditingkatkan yang bekerja ke arah mana saja dan mengembalikan kecocokan persis secara default, memudahkan dan lebih mudah digunakan daripada sebelumnya.
Gunakan VLOOKUP saat Anda perlu menemukan berbagai hal dalam tabel atau rentang menurut baris. Misalnya, mencari harga komponen otomotif menurut nomor komponennya, atau mencari nama karyawan berdasarkan ID karyawan mereka.
Dalam bentuknya yang paling sederhana, fungsi VLOOKUP mengatakan:
=VLOOKUP(Apa yang ingin Anda cari, di mana Anda ingin mencarinya, nomor kolom di dalam rentang berisi nilai untuk dikembalikan, mengembalikan hasil yang Hampir sama atau Persis – yang diindikasikan sebagai 1/TRUE, atau 0/FALSE).
Tips: Cara menggunakan VLOOKUP adalah menata data Anda sehingga nilai yang anda cari (Buah) berada di sebelah kiri dari nilai kembali (Jumlah) yang ingin ditemukan.
Gunakan fungsi VLOOKUP untuk mencari nilai dalam tabel.
Sintaks
VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Misalnya:
-
=VLOOKUP(A2,A10:C20,2,TRUE)
-
=VLOOKUP("Febriansyah",B2:E7,2,FALSE)
-
=VLOOKUP(A2,'Detail Klien'! A:F,3,FALSE)
| lookup_value (diperlukan) | Nilai yang ingin Anda cari. Nilai yang ingin Anda cari harus berada di kolom pertama rentang sel yang Anda tentukan dalam argumen table_array argumen. Misalnya, jika table-array membentang di sel B2:D7, maka sel lookup_value harus berada di kolom B. Lookup_value dapat berupa nilai atau referensi ke sel. |
| table_array (diperlukan) | Rentang sel yang akan dicari VLOOKUP untuk lookup_value dan nilai kembali. Anda bisa menggunakan rentang bernama atau tabel, dan Anda bisa menggunakan nama dalam argumen sebagai ganti referensi sel. Kolom pertama dalam rentang sel harus berisi nilai lookup_value. Rentang sel juga perlu menyertakan nilai kembali yang ingin Anda temukan. Pelajari cara memilih rentang di lembar kerja. |
| col_index_num (diperlukan) | Nomor kolom (dimulai dengan 1 untuk kolom paling kiri table_array) yang berisi nilai kembali. |
| range_lookup (opsional) | Nilai logika yang menetapkan apakah Anda ingin VLOOKUP menemukan hasil yang sama persis atau yang mendekati:
|
Terdapat empat potongan informasi yang akan diperlukan untuk membangun sintaks VLOOKUP:
-
Nilai yang ingin Anda cari, juga disebut nilai pencarian.
-
Rentang tempat nilai pencarian berada. Ingat bahwa nilai pencarian harus selalu berada di kolom pertama dalam rentang agar VLOOKUP berfungsi dengan benar. Sebagai contoh, jika nilai pencarian berada dalam sel C2, rentang Anda harus dimulai dengan C.
-
Nomor kolom dalam rentang yang berisi nilai kembali. Misalnya, jika Anda menentukan B2:D11 sebagai rentangnya, Anda harus menghitung B sebagai kolom pertama, C sebagai kedua, dan sebagainya.
-
Secara opsional, Anda dapat menentukan TRUE jika menginginkan hasil yang mendekati atau FALSE jika menginginkan hasil yang sama persis dari nilai kembali. Jika Anda tidak menentukan apa pun, nilai default akan selalu menjadi TRUE atau hasil yang mendekati.
Sekarang, letakkan semua hal yang disebutkan di atas sebagai berikut:
=VLOOKUP(nilai pencarian, rentang berisi nilai pencarian, nomor kolom dalam rentang berisi nilai kembali, Hasil yang hampir sama (TRUE) atau Hasil yang sama persis (FALSE)).
Berikut ini beberapa contoh VLOOKUP:
Anda dapat menggunakan VLOOKUP untuk menggabungkan beberapa tabel menjadi satu, selama salah satu tabel memiliki bidang yang sama dengan yang lainnya. Hal ini akan sangat berguna khususnya jika Anda perlu berbagi buku kerja dengan orang-orang yang memiliki Excel versi lama yang tidak mendukung fitur data dengan beberapa tabel sebagai sumber data, dengan menggabungkan sumber ke dalam satu tabel dan mengubah sumber data fitur data ke tabel yang baru, fitur data dapat digunakan dalam versi Excel yang lebih lama (jika fitur data itu sendiri didukung oleh versi yang lebih lama).
| |
| Di sini, kolom A-F dan H memiliki nilai atau rumus yang hanya menggunakan nilai pada lembar kerja, dan kolom lainnya menggunakan VLOOKUP dan nilai kolom A (Kode Klien) dan kolom B (Attorney) untuk mendapatkan data dari tabel lainnya. |
-
Salin tabel yang memiliki bidang umum ke lembar kerja baru, dan beri nama.
-
Klik Pusat > Data >Hubungan Untuk membuka kotak dialog Kelola Hubungan.
-
Untuk setiap hubungan tercantum, perhatikan yang berikut ini:
-
Bidang yang menautkan tabel (tercantum dalam tanda kurung dalam kotak dialog). Ini adalah lookup_value untuk rumus VLOOKUP Anda.
-
Nama Tabel Pencarian Terkait. Inilah rumus table_array rumus VLOOKUP Anda.
-
Bidang (kolom) dalam Tabel Pencarian Terkait yang memiliki data yang Anda inginkan di kolom baru Anda. Informasi ini tidak diperlihatkan dalam dialog Kelola Hubungan - Anda harus melihat Tabel Pencarian Terkait untuk melihat bidang mana yang ingin Anda ambil. Anda ingin mencatat nomor kolom (A=1) - ini adalah col_index_num dalam rumus Anda.
-
-
Untuk menambahkan bidang ke tabel baru, masukkan rumus VLOOKUP Anda di kolom kosong pertama menggunakan informasi yang Anda kumpulkan di langkah 3.
Dalam contoh kami, kolom G menggunakan Attorney (the lookup_value) untuk mendapatkan data Bill Rate dari kolom keempat (col_index_num = 4) dari tabel lembar kerja Attorneys, tblAttorneys (table_array), dengan rumus =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).
Rumus juga bisa menggunakan referensi sel dan referensi rentang. Dalam contoh kami, akan menjadi =VLOOKUP(A2,'Attorneys'! A:D,4,FALSE).
-
Terus tambahkan bidang hingga Anda memiliki semua bidang yang Anda perlukan. Jika Anda mencoba menyiapkan buku kerja yang berisi fitur data yang menggunakan beberapa tabel, ubah sumber data fitur data ke tabel baru.
| Nilai yang dikembalikan salah | Jika range_lookup adalah TRUE atau dihilangkan, kolom pertama harus diurutkan menurut abjad. Jika kolom pertama tidak diurutkan, nilai kembali mungkin dapat berupa sesuatu yang tidak Anda harapkan. Urutkan kolom itu terlebih dulu, atau gunakan FALSE untuk kecocokan persis. |
| #N/A dalam sel |
Untuk informasi selengkapnya tentang mengatasi kesalahan #N/A dalam VLOOKUP, lihat Cara mengoreksi kesalahan #N/A dalam fungsi VLOOKUP. |
| #REF! dalam sel | Jika col_index_num lebih besar dari jumlah kolom dalam table-array,Anda akan mendapatkan nilai #REF! nilai kesalahan. Untuk informasi selengkapnya dalam mengatasi #REF! dalam VLOOKUP, lihat Cara mengoreksi #REF!. |
| #VALUE! dalam sel | Jika table_array kurang dari 1, Anda akan mendapatkan hasil yang #VALUE! nilai kesalahan. Untuk informasi selengkapnya dalam mengatasi kesalahan #VALUE! dalam VLOOKUP, lihat Cara mengoreksi kesalahan #VALUE! dalam fungsi VLOOKUP. |
| #NAME? dalam sel | Nilai kesalahan #NAME? biasanya tanda petik rumus tidak lengkap. Untuk mencari nama seseorang, pastikan Anda menggunakan tanda kutip di sekitar nama di dalam rumus. Misalnya, masukkan nama sebagai "Febriansyah" di =VLOOKUP("Febriansyah" ,B2:E7,2,FALSE). Untuk informasi selengkapnya, lihat Cara mengoreksi #NAME!. |
| Kesalahan #LUAPAN! dalam sel | Kesalahan khusus #SPILL! biasanya berarti bahwa rumus Anda mengandalkan irisan implisit untuk nilai pencarian, dan menggunakan seluruh kolom sebagai referensi. Misalnya, =VLOOKUP(A:A,A:C,2,FALSE). Anda dapat mengatasi masalah tersebut dengan menjangkarkan referensi pencarian dengan operator @ seperti ini: =VLOOKUP(@A:A,A:C,2,FALSE). Alternatifnya, Anda dapat menggunakan metode VLOOKUP tradisional dan merujuk pada satu sel, bukan seluruh kolom: =VLOOKUP(A2,A:C,2,FALSE). |
| Gunakan referensi absolut untuk range_lookup | Menggunakan referensi absolut memungkinkan Anda mengisi rumus sehingga rumus selalu mencari rentang pencarian yang sama. Pelajari cara menggunakan referensi sel absolut. |
| Jangan simpan angka atau tanggal sebagai teks. | Ketika mencari nilai angka atau tanggal, pastikan data yang berada di kolom pertama table_array tidak disimpan sebagai nilai teks. Jika tidak, VLOOKUP dapat mengembalikan nilai yang salah atau yang tak diharapkan. |
| Urutkan kolom pertama | Urutkan kolom pertamatable_array sebelum menggunakan VLOOKUP saat range_lookup adalah TRUE . |
| Gunakan karakter wildcard | Jika range_lookup FALSE dan lookup_value merupakan teks, Anda dapat menggunakan karakter wildcard—tanda tanya (?) dan tanda bintang (*)—dalam lookup_value. Tanda tanya cocok dengan karakter tunggal apa pun. Tanda bintang cocok dengan urutan karakter apa pun. Jika Anda ingin menemukan tanda tanya atau tanda bintang yang sebenarnya, ketikkan tilde (~) sebelum karakter. Misalnya, =VLOOKUP("Fontan?",B2:E7,2,FALSE) akan mencari semua contoh Fontana dengan huruf terakhir yang dapat bervariasi. |
| Pastikan data Anda tidak mengandung karakter yang salah. | Ketika mencari nilai teks di kolom pertama, pastikan bahwa data di kolom pertama tidak memiliki spasi awal, spasi akhir, penggunaan tanda kutip lurus ( ' atau " ) dan lengkung ( ‘ atau “) yang tidak konsisten, atau karakter non-cetak. Dalam kasus ini, VLOOKUP mungkin mengembalikan nilai yang salah atau tidak diharapkan. Untuk mendapatkan hasil yang akurat, coba gunakan fungsi CLEAN atau fungsi TRIM untuk menghapus spasi akhir setelah nilai tabel dalam sel. |
Anda dapat bertanya kapan saja kepada pakar di Komunitas Teknologi Excel atau mendapatkan dukungan di Komunitas Jawaban.
Kartu Referensi Cepat: Penyegaran VLOOKUP
Kartu Referensi Praktis: Tips pemecahan masalah
VLOOKUP Cara memperbaiki kesalahan #VALUE! dalam fungsi VLOOKUP
Cara memperbaiki #N/A dalam fungsi VLOOKUP
Gambaran umum rumus dalam Excel
Cara menghindari rumus yang rusak
Mendeteksi kesalahan dalam rumus
Excel alfabetis
Excel baru (menurut kategori)
VLOOKUP (pratinjau gratis)