Video: Belajar Rumus Vlookup dan Hlookup 2024
Yang paling populer dari fungsi pencarian 201620 adalah fungsi HLOOKUP (untuk Horizontal Lookup) dan VLOOKUP (untuk Vertical Lookup). 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.
menunjuk kolom tabel pencarian yang berisi nilai yang dikembalikan oleh fungsi VLOOKUP berdasarkan pada pencocokan nilai argumen lookup_value terhadap orang-orang dalam argumen table_array. Anda menentukan argumen col_index_num yang menghitung berapa banyak kolom kolom ini berada di sebelah kanan dari kolom pertama tabel Lookup vertikal, dan Anda menyertakan kolom pertama tabel Lookup dalam hitungan ini. Argumen row_index_num
menunjuk baris yang berisi nilai dikembalikan oleh fungsi HLOOKUP dalam tabel horizontal. Anda menentukan argumen row_index_num dengan menghitung berapa banyak baris dari baris ini berasal dari baris atas tabel Lookup horizontal. Sekali lagi, Anda menyertakan baris teratas tabel Lookup dalam hitungan ini. Saat memasukkan argumen col_index_num
atau row_index_num pada fungsi VLOOKUP dan HLOOKUP, nilai yang Anda masukkan tidak melebihi jumlah kolom atau baris dalam tabel Lookup. 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_arrayBila 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.
Menggunakan fungsi VLOOKUP untuk mengembalikan jumlah tip yang akan ditambahkan dari tabel Lookup.
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
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 di 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 pada gambar menggunakan fungsi IF untuk menentukan argumen col_index_num untuk Fungsi VLOOKUP di sel F3. Fungsi IF menentukan nomor kolom yang akan digunakan pada tabel tip dengan mencocokkan persentase yang dimasukkan dalam Tip_Percentage (sel F1) dengan 0.15. Jika cocok, fungsi mengembalikan 2 sebagai argumen
col_index_num dan fungsi VLOOKUP mengembalikan nilai dari kolom kedua (kolom 15% B) pada 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) pada 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: Menggunakan fungsi HLOOKUP untuk mengembalikan harga item roti dari tabel Lookup. = 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
dan nama rentang sel Tabel harga yang diberikan ke 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 dalam Penjualan Harian daftar harus sama persis dengan nama item di tabel Harga 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.
