Belajar Rumus VLOOKUP & HLOOKUP | Tutorial Excel

Summary

In this video, the presenter explains how to use the 'VLOOKUP' and 'HLOOKUP' formulas in Excel for efficiently retrieving data based on a reference. The 'VLOOKUP' formula is used for vertical lookups, searching for product names and prices from a given table based on product codes. The presenter demonstrates how to lock the reference table and adjust column indices for accurate results. Then, the 'HLOOKUP' formula is introduced for horizontal lookups, applied to search for product names and types from a row-based table. The video concludes with helpful formatting tips for making the data display correctly.

Takeaways

• 😀 Learn how to use the VLOOKUP formula in Excel to retrieve data based on a given product code.
• 😀 VLOOKUP allows you to search vertically within a table to find related data, such as product names and prices.
• 😀 To use VLOOKUP, input the lookup value (product code), specify the table range, and lock it with F4 to prevent changes when copying the formula.
• 😀 Use the column index number in VLOOKUP to specify which column contains the data you want to retrieve, such as the product name (column 2).
• 😀 VLOOKUP can be set to use either TRUE (approximate match) or FALSE (exact match), with FALSE recommended for precise data retrieval.
• 😀 The tutorial also demonstrates how to apply the VLOOKUP formula to retrieve multiple product names and prices by dragging the formula down.
• 😀 HLOOKUP, another Excel function, is introduced as a horizontal lookup alternative to VLOOKUP, useful when headers are placed in rows rather than columns.
• 😀 To use HLOOKUP, define the lookup value, lock the table range with F4, and specify the row number that contains the data you need.
• 😀 Just like VLOOKUP, HLOOKUP can be set to TRUE (approximate match) or FALSE (exact match), with FALSE recommended for accuracy.
• 😀 The video also covers how to format the result, such as adjusting decimal places for prices to make the data look cleaner and more readable.

Q & A

What is the primary purpose of the VLOOKUP function demonstrated in the video?

The VLOOKUP function is used to search for data in a table based on a reference value, such as a product code, and retrieve related information, like the product name or price, from a specific column in the table.

Why does the speaker use the 'FALSE' argument in the VLOOKUP formula?

The 'FALSE' argument is used to specify that an exact match is required when searching for the value in the first column of the table. This ensures that the correct product is found based on the provided product code.

What is the significance of the dollar signs ($) in the table range of the VLOOKUP formula?

The dollar signs are used to create absolute references in the formula, meaning the table range ($A$2:$C$8) will not change when the formula is dragged down or copied to other cells.

What does the column index number represent in the VLOOKUP function?

The column index number specifies the column in the table from which the corresponding data should be retrieved. For example, '2' retrieves data from the second column (product name), while '4' would retrieve data from the fourth column (price).

What is the purpose of using the F4 key while writing the VLOOKUP formula?

Pressing F4 locks the table range (making it absolute) so that it doesn’t change when the formula is copied or dragged to other cells. This ensures consistency in the lookup range.

How does the speaker demonstrate the use of the HLOOKUP function in the video?

The HLOOKUP function is used when the data is arranged horizontally (with headers in the first row). It helps retrieve information like product names and types based on the product code, but instead of searching vertically, it searches across rows.

What is the difference between VLOOKUP and HLOOKUP as shown in the video?

VLOOKUP is used for vertical lookups, where data is organized in columns, while HLOOKUP is used for horizontal lookups, where data is arranged in rows.

Why does the speaker use the number '2' as the row index in the HLOOKUP formula for retrieving product names?

The number '2' represents the second row in the table where the product names are located. The row index number indicates which row to search within the table.

How can the user modify the price display in Excel after using the VLOOKUP function?

To modify the price display, the user can format the cell as currency or adjust the number of decimal places by clicking the currency style option and reducing the number of decimal places.

What are the practical applications of using the VLOOKUP and HLOOKUP formulas in Excel, as demonstrated in the video?

These formulas are particularly useful for efficiently looking up and retrieving specific information from large datasets, such as product details (name, type, price) based on a reference code, saving time and reducing manual errors.