Rumus
VLOOKUP dan HLOOKUP Pada Microsoft Excel
Khusus Untuk tugas Simulasi Digital, tentang tentang kegunaan dari VLOOKUP dan HLOOKUP
A.
Cara Menggunakan Rumus VLOOKUP
Rumus VLOOKUP sangat banyak
kegunaannya, fungsi ini sudah ada sejak Excel versi awal atau generasi pertama
sampai Excel versi terbaru. Fungsi VLOOKUP adalah fungsi Excel yang digunakan
untuk mencari dan mengambil data dari suatu tabel referensi berdasarkan nilai
tertentu.
Seperti namanya VLOOKUP yang diambil dari singkatan vertical lookup yang
berarti mencari data secara vertikal, karena di Excel juga terdapat fungsi
khusus untuk mencari data secara horizontal yaitu HLOOKUP atau horizontal
lookup.
Penulisan Rumus VLOOKUP adalah
sebagai berikut:
=VLOOKUP(lookup_value;table_array;col_index_num;[range_lookup])
Keterangan:
lookup_value
: nilai yang akan dicari di kolom pertama tabel atau rentang.
table_array : tabel referensi yang ingin dicari datanya yang kolom pertamanya
digunakan
sebagai acuan untuk
nilai lookup_value col_index_num : nomor kolom pada
table_array yang ingin di cari
datanya.
range_lookup
: pilihan untuk menentukan nilai yang persis atau yang mendekati.
TRUE (1) untuk
mencari nilai yang mendekati.
FALSE (2) untuk
mencari nilai yang sama persis Jika range_lookup
dikosongkan atau
tidak di isi maka otomatis dianggap sebagai TRUE.
Contoh penerapannya seperti gambar
berikut ini:
Pada gambar diatas kita mencari data
nama dan alamat berdasarkan nomor urut, di gambar tersebut terlihat sel C13 sebagai lookup_value
atau data yang ingin dicari, data ini berdasarkan kolom pertama dari kiri pada
tabel referensi dan tabel referensinya (tabel_array)
adalah sel B5 sampai D9.
Pada sel C14 karena kita ingin mencari data nama maka pada col_index_num-nya kita masukkan angka 2 karena kolom
nama terletak di kolom kedua dari tabel referensi.
Pada cel C15 karena kita ingin mencari data alamat maka pada col_index_num-nya kita masukkan angka 3 karena kolom
alamat terletak di kolom ketiga dari tabel referensi.
Pada range_lookup-nya
di masukkan pilihan FALSE agar menampilkan nilai yang sama persis, dan bila
pada sel C13 kita masukkan angka yang tidak terdapat dalam kolom pertama tabel
referensi misal angka 6 atau 7 maka hasilnya adalah #N/A. Berbeda jika kita
mengganti FALSE dengan TRUE yang berarti menampilkan nilai dari yang mendekati,
misal kita memasukkan angka 6 atau 7 maka yang ditampilkan adalah data dari
angka 5 karena yang lebih dekat ke 6 atau 7 adalah angka 5 maka hasilnya adalah
Firman Jakarta.
Rumus VLOOKUP bisa diterapkan pada satu sheet yang sama, atau bisa juga berbeda sheet baik dengan memasukkan alamat sel langsung atau bisa juga dengan membuat define name.
Fungsi VLOOKUP dalam satu Sheet
Rumus VLOOKUP bisa diterapkan pada satu sheet yang sama, atau bisa juga berbeda sheet baik dengan memasukkan alamat sel langsung atau bisa juga dengan membuat define name.
Fungsi VLOOKUP dalam satu Sheet
Untuk menggunakan rumus VLOOKUP
dalam satu sheet atau pada sheet yang sama adalah sama seperti contoh yang
sudah saya jelaskan diatas jadi gak perlu saya ulangi lagi ya caranya, karena
pasti anda sudah bisa atau mengerti dari contoh gambar diatas
Fungsi VLOOKUP dari Data pada Sheet Lain
Fungsi VLOOKUP dari Data pada Sheet Lain
Untuk
menggunakan rumus VLOOKUP dari data sheet yang lain atau beda sheet, misal data
tabel referensinya adalah di Sheet1 dan kita ingin menampilkan/memanggil data
tersebut dari Sheet2 atau Sheet3 dan sebagainya maka penulisan rumusnya adalah:
=VLOOKUP(lookup_value;Sheet1!table_array;col_index_num;[range_lookup])
=VLOOKUP(lookup_value;Sheet1!table_array;col_index_num;[range_lookup])
Anda bisa
mengubah nama Sheet1 dengan nama sheets dimana data tabel referensi
ditempatkan. contohnya sebagai gambar berikut ini:
Fungsi VLOOKUP dengan
Define Name
Selain menuliskan alamat tabel_array
langsung pada rumus VLOOKUP seperti B5:D9 atau Sheet1!B5:B9 kita juga bisa
menggantinya dengan define name atau menamai range tersebut. caranya adalah:
1.
Blok semua data yang dijadikan
sebagai tabel referensi, contohnya pada sel B5 sampai D9.
2.
Arahkan cursor ke Name Box dan ubah namanya menjadi apa
saja, misal Data Penting setelah itu tekan Enter
untuk mengahirinya.
Untuk penerapannya tinggal ubah saja
data tabel_array dengan define name (name box) tersebut. contoh diterapkan pada
VLOOKUP beda sheet. menjadi seperti gambar berikut ini:
Dengan memanfaatkan fungsi define
name ini, pemanggilan data tabel referensi jadi lebih mudah dan praktis karena
bisa diterapkan di Sheet mana saja.
B.
Cara Menggunakan Rumus HLOOKUP
Deskripsi
Mencari nilai di baris teratas tabel
atau array nilai, lalu mengembalikan nilai di kolom yang sama dari baris yang
Anda tentukan dalam tabel atau array. Gunakan HLOOKUP jika nilai perbandingan
terletak di sebuah baris di bagian atas tabel data, dan Anda ingin mencari ke
beberapa baris tertentu di bawahnya. Gunakan VLOOKUP jika nilai perbandingan
terletak di kolom sebelah kiri data yang ingin dicari.
Huruf H pada HLOOKUP merupakan
singkatan dari "Horizontal."
Sintaks
HLOOKUP(lookup_value, table_array,
row_index_num, [range_lookup])
Sintaks fungsi HLOOKUP memiliki
argumen berikut:
- Lookup_value Diperlukan. Nilai yang dicari di baris pertama tabel. Lookup_value bisa berupa nilai, referensi, atau string teks.
- Table_array Diperlukan. Tabel informasi tempat data dicari. Gunakan referensi ke sebuah rentang atau nama rentang.
- Nilai di baris pertama table_array bisa berupa teks, angka, atau nilai logika.
- Jika range_lookup TRUE, nilai di baris pertama table_array harus diletakkan dalam urutan naik: ...-2, -1, 0, 1, 2,... , A-Z, FALSE, TRUE; jika tidak, HLOOKUP tidak akan memberi nilai yang benar. Jika range_lookup FALSE, table_array tidak perlu diurutkan.
- Teks huruf besar dan huruf kecil sama.
- Urutkan nilai dengan urutan naik, kiri ke kanaan.
- Row_index_num Diperlukan. Nomor baris dalam table_array yang akan mengembalikan nilai yang cocok. Argumen row_index_num 1 mengembalikan nilai baris pertama dalam table_array, row_index_num 2 mengembalikan nilai baris kedua dalam table_array, dan seterusnya. Jika row_index_num kurang dari 1, HLOOKUP mengembalikan nilai kesalahan #VALUE!; jika row_index_num lebih besar dari jumlah baris di table_array, HLOOKUP mengembalikan nilai kesalahan #REF!.
- Range_lookup Opsional. Nilai logika yang menentukan apakah Anda ingin HLOOKUP mencari kecocokan persis atau kecocokan yang mendekati. Jika TRUE atau dihilangkan, dikembalikan sebuah kecocokan yang mendekati. Dengan kata lain, jika kecocokan persis ditemukan, dihasillkan nilai terbesar berikutnya yang kurang dari lookup_value. Jika FALSE, HLOOKUP akan menemukan kecocokan persis. Jika tidak ditemukan, dikembalikan nilai kesalahan #N/A.
Keterangan
- Jika HLOOKUP tidak dapat menemukan lookup_value, dan range_lookup TRUE, maka digunakan nilai terbesar yang kurang dari lookup_value.
- Jika lookup_value lebih kecil daripada nilai terkecil dalam baris pertama table_array, HLOOKUP mengembalikan nilai kesalahan #N/A.
- Jika range_lookup FALSE dan lookup_value berupa teks, Anda dapat menggunakan karakter wildcard, tanda tanya (?) dan tanda bintang (*), di lookup_value. Sebuah tanda tanya akan dicocokkan dengan satu karakter; sedangkan tanda bintang akan mencocokkan urutan karakter. Jika Anda ingin menemukan tanda tanya atau tanda bintang, ketikkan tilde (~) sebelum karakter.
Salin contoh data di dalam tabel
berikut ini dan tempel ke dalam sel A lembar kerja Excel yang baru. Agar rumus
menunjukkan hasil, pilih datanya, tekan F2, lalu tekan Enter. Jika perlu, Anda
bisa menyesuaikan lebar kolom untuk melihat semua data.
|
Poros
|
Bantalan
|
Baut
|
|
4
|
4
|
9
|
|
5
|
7
|
10
|
|
6
|
8
|
11
|
|
Rumus
|
Deskripsi
|
Hasil
|
|
=HLOOKUP("Poros", A1:C4,
2, TRUE)
|
Mencari "Poros" di baris
1, dan mengembalikan nilai dari baris 2 yang berada di kolom yang sama (kolom
A).
|
4
|
|
=HLOOKUP("Bantalan",
A1:C4, 3, FALSE)
|
Mencari "Bantalan" di baris
1, dan mengembalikan nilai dari baris 3 yang berada di kolom yang sama (kolom
B).
|
7
|
|
=HLOOKUP("B", A1:C4, 3,
TRUE)
|
Mencari "B" di baris 1,
dan mengembalikan nilai dari baris 3 yang berada di kolom yang sama. Karena
kecocokan yang persis untuk "B" tidak ditemukan, nilai terbesar
dalam baris 1 yang kurang dari "B" digunakan: "Poros," di
kolom A.
|
5
|
|
=HLOOKUP("Baut", A1:C4,
4)
|
Mencari "Baut" di baris
1, dan mengembalikan nilai dari baris 4 yang berada di kolom yang sama (Kolom
C)
|
11
|
|
=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"},
2, TRUE)
|
Mencari angka 3 di konstanta array
tiga baris, dan mengembalikan nilai dari baris 2 di kolom yang sama (dalam
hal ini, ketiga). Ada tiga baris nilai dalam konstanta array, setiap baris
dipisahkan oleh titik koma (;). Karena "c" ditemukan di baris 2 dan
di kolom yang sama dengan 3, "c" dikembalikan.
Contoh File VLOOKUP silahkan download di bawah ini --> Contoh Penggunaan VLOOKUP.xlsx
|
