TERNYATA GAMPANG BANGET BELAJAR FUNGSI VLOOKUP & HLOOKUP | Materi Informatika Kelas 8

Summary

In this tutorial, the presenter demonstrates how to implement the VLOOKUP and HLOOKUP functions in Microsoft Excel to efficiently fill in missing data. Using a practical example with two reference tables (one vertical and one horizontal), the video explains how to use VLOOKUP for vertical searches and HLOOKUP for horizontal searches. Step-by-step, the process covers applying these functions to look up customer names, residences, and delivery times using order numbers and other relevant data. The video is designed to help users improve their Excel skills with these powerful data lookup functions.

Takeaways

• 😀 Learn how to implement VLOOKUP and HLOOKUP functions in Microsoft Excel.
• 😀 Understand the difference between vertical (VLOOKUP) and horizontal (HLOOKUP) tables for using the functions.
• 😀 VLOOKUP is used when the data in the reference table is organized vertically (column-based).
• 😀 HLOOKUP is used when the data in the reference table is organized horizontally (row-based).
• 😀 Use the 'LookUp Value' from the main data to search for corresponding values in the reference table.
• 😀 Always lock table ranges with F4 or FN + F4 to prevent the formula from shifting when dragged down.
• 😀 The 'Index Number' in VLOOKUP/HLOOKUP specifies which column or row in the reference table to return.
• 😀 The 'Range Lookup' parameter is used to choose between exact or approximate matches. Use 'False' (0) for exact matches.
• 😀 Apply the VLOOKUP formula to fill in missing data, such as customer names or domicile information, from the reference table.
• 😀 Use the HLOOKUP function when you need to retrieve data from a reference table that is arranged horizontally.
• 😀 The tutorial shows how to automate filling data across multiple rows using the drag feature in Excel.

Q & A

What are the main functions being discussed in the video?

The main functions being discussed are VLOOKUP and HLOOKUP in Microsoft Excel, which are used for looking up data from reference tables.

When should you use the VLOOKUP function in Excel?

You should use VLOOKUP when the reference table is organized vertically, with data arranged in columns.

What is the difference between VLOOKUP and HLOOKUP?

VLOOKUP is used when the reference table is vertical (data in columns), while HLOOKUP is used for horizontal tables (data in rows).

How do you structure the VLOOKUP formula?

The VLOOKUP formula is structured as =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup), where you specify the lookup value, the reference table, the column number for the desired data, and the lookup type (exact or approximate).

What does the 'range_lookup' argument in VLOOKUP mean?

'range_lookup' determines whether you want an exact match (set to FALSE) or an approximate match (set to TRUE). In this video, FALSE is used to find exact matches.

How do you lock the reference table in the VLOOKUP formula?

You lock the reference table by pressing F4 (or FN + F4), which will insert dollar signs ($) in the table array to prevent it from shifting when you drag the formula.

Why is the 'col_index_num' important in the VLOOKUP formula?

The 'col_index_num' specifies the column in the reference table from which the data should be retrieved. For example, if you're looking for customer names, you set it to 2, as the customer names are in the second column of the reference table.

What does the HLOOKUP function do, and how is it different from VLOOKUP?

HLOOKUP works similarly to VLOOKUP but is used when the reference table is organized horizontally, with data arranged in rows. The syntax for HLOOKUP is =HLOOKUP(lookup_value, table_array, row_index_num, range_lookup).

How do you use HLOOKUP to find delivery times in the example?

To use HLOOKUP for delivery times, you set the lookup value to the customer's location, use the reference table for delivery times (in rows), and set the row index number to 2, as the delivery times are in the second row.

What does the 'row_index_num' argument mean in HLOOKUP?

In HLOOKUP, the 'row_index_num' specifies which row in the reference table contains the data you're looking for. For delivery times, this would be row 2 in the reference table.

What is the process for filling in the customer names, locations, and delivery times using the formulas?

To fill in the customer names, locations, and delivery times, you use VLOOKUP for vertical data (like customer names and locations) and HLOOKUP for horizontal data (like delivery times). You input the formulas, lock the reference tables, adjust the index numbers, and drag the formulas down to fill in the rest of the data.