Video: 4 Macam Rumus VLOOKUP 2024
Fungsi pencarian yang paling populer di Excel 2013 adalah fungsi HLOOKUP (untuk Horizontal Lookup) dan VLOOKUP (untuk Vertikal). Fungsi-fungsi ini terletak pada menu drop-down Lookup & Reference pada tab Rumus Ribbon dan juga dalam kategori Lookup & Reference pada kotak dialog Insert Function.
Mereka adalah bagian dari kelompok fungsi yang kuat yang dapat mengembalikan nilai dengan melihat mereka di tabel data.
Fungsi VLOOKUP menggunakan sintaks berikut:
Fungsi HLOOKUP mengikuti sintaks yang hampir sama:
HLOOKUP (lookup_value, table_array, row_index_num, [range_lookup]) > Dalam kedua fungsi tersebut, argumen
lookup_value
adalah nilai yang ingin Anda cari di tabel Lookup, dan table_array adalah rentang sel atau nama tabel Lookup yang berisi keduanya. nilai untuk melihat ke atas dan nilai yang terkait untuk kembali.
pada fungsi VLOOKUP adalah jumlah kolom yang nilainya dibandingkan dengan argumen lookup_value dalam tabel vertikal. Argumen row_index_num pada fungsi HLOOKUP adalah jumlah baris yang nilainya dibandingkan dengan tabel lookup_value dalam tabel horizontal. Saat memasukkan argumen col_index_num
atau row_index_num pada fungsi VLOOKUP dan HLOOKUP, Anda harus memasukkan nilai lebih besar dari nol yang tidak melebihi jumlah kolom atau baris di Tabel pencarian Argumen opsional range_lookup
di VLOOKUP dan fungsi HLOOKUP adalah TRUE logis atau FALSE yang menentukan apakah Anda ingin Excel menemukan pencocokan tepat atau perkiraan untuk tampilan lookup_value di table_array Bila Anda menentukan TRUE atau menghilangkan argumen range_lookup
pada fungsi VLOOKUP atau HLOOKUP, Excel akan menemukan perkiraan pencocokan. Bila Anda menentukan FALSE sebagai argumen range_lookup , Excel hanya menemukan kecocokan yang tepat. Menemukan perkiraan kecocokan hanya bila Anda mencari entri numerik (bukan teks) di kolom atau baris pertama tabel Lookup vertikal atau horizontal.Ketika Excel tidak menemukan pencocokan tepat di kolom atau baris Lookup ini, ia menempatkan nilai tertinggi berikutnya yang tidak melebihi argumen lookup_value
dan kemudian mengembalikan nilai pada kolom atau baris yang ditunjuk oleh < col_index_num atau row_index_num argumen. Bila menggunakan fungsi VLOOKUP dan HLOOKUP, entri teks atau numerik di kolom Lookup atau baris (yaitu kolom paling kiri dari tabel Lookup vertikal atau baris atas tabel Lookup horizontal) harus unik. Entri ini juga harus diatur atau diurutkan dalam urutan naik; yaitu urutan abjad untuk entri teks, dan urutan terendah untuk entri numerik. Angka tersebut menunjukkan contoh penggunaan fungsi VLOOKUP untuk mengembalikan ujung 15% atau 20% dari tabel tip, tergantung pada total pretax cek. Cell F3 berisi fungsi VLOOKUP: = VLOOKUP (Pretax_Total, Tip_Table, IF (Tip_Percentage = 0 15, 2, 3))
Formula ini mengembalikan jumlah tip berdasarkan persentase tip di sel F1 dan jumlah pretax cek di sel F2.
Untuk menggunakan tabel tip ini, masukkan persentase tip (15% atau 20%) di sel F1 (diberi nama Tip_Percentage) dan jumlah cek sebelum pajak di sel F2 (bernama Pretax_Total). Excel kemudian mencari nilai yang Anda masukkan di sel Pretax_Total di kolom pertama tabel Lookup, yang mencakup rentang sel A2: C101 dan diberi nama Tip_Table.
Excel kemudian memindahkan nilai di kolom pertama Tip_Table sampai menemukan kecocokan, dimana program menggunakan argumen
col_index_num
pada fungsi VLOOKUP untuk menentukan jumlah tip dari baris tabel mana kembali ke sel F3
Jika Excel menemukan bahwa nilai yang dimasukkan ke dalam sel Pretax_Total ($ 16. 50 dalam contoh ini) tidak sesuai dengan salah satu nilai di kolom pertama Tip_Table, program terus menelusuri rentang perbandingan sampai menemukan nilai pertama yang melebihi jumlah pretax (17. 00 pada sel A19 dalam contoh ini). Excel kemudian bergerak kembali ke baris sebelumnya di tabel dan mengembalikan nilai pada kolom yang sesuai dengan argumen col_index_num
dari fungsi VLOOKUP. (Ini karena argumen opsional
range_lookup telah diabaikan dari fungsinya.) Perhatikan bahwa contoh tabel tip menggunakan fungsi IF untuk menentukan argumen col_index_num untuk fungsi VLOOKUP di sel F3.
Fungsi IF menentukan nomor kolom yang akan digunakan di tabel tip dengan mencocokkan persentase yang dimasukkan di Tip_Percentage (sel F1) dengan 0. 15. Jika cocok, fungsi mengembalikan 2 sebagai col_index_num argumen dan fungsi VLOOKUP mengembalikan nilai dari kolom kedua (kolom 15% B) di kisaran Tip_Table.
Jika tidak, fungsi IF mengembalikan 3 sebagai argumen col_index_num dan fungsi VLOOKUP mengembalikan nilai dari kolom ketiga (kolom 20% C) di kisaran Tip_Table.
Gambar berikut menunjukkan contoh yang menggunakan fungsi HLOOKUP untuk mencari harga setiap item roti yang tersimpan dalam tabel Lookup harga terpisah dan kemudian mengembalikan harga itu ke kolom Price / Doz dari daftar Penjualan Harian.Cell F3 berisi formula asli dengan fungsi HLOOKUP yang kemudian disalin ke bawah kolom F: = HLOOKUP (item, Price_table, 2, FALSE) Dalam fungsi HLOOKUP ini, nama rentang Item yang diberikan ke kolom Item di kisaran C3: C62 didefinisikan sebagai argumen
lookup_value
dan nama rentang sel Tabel harga yang diberikan pada kisaran sel I1: M2 adalah argumen
table_array . Argumen row_index_num adalah 2 karena Anda ingin Excel mengembalikan harga di baris kedua tabel Harga Penawaran, dan argumen opsional
range_lookup adalah FALSE karena nama item di daftar Penjualan Harian harus sama persis dengan nama item di tabel Price Lookup. Dengan memiliki fungsi HLOOKUP, gunakan kisaran tabel Harga untuk memasukkan harga per lusin untuk setiap item barang roti dalam daftar Penjualan Harian, Anda menjadikannya masalah yang sangat sederhana untuk memperbarui salah satu penjualan dalam daftar. Yang harus Anda lakukan adalah mengubah biaya Price / Doz dalam kisaran ini, dan fungsi HLOOKUP segera memperbarui harga baru di daftar Penjualan Harian dimanapun barang terjual.
