BELAJAR VLOOKUP DENGAN MUDAH

Summary

This video script provides a detailed tutorial on using the VLOOKUP function in Excel to retrieve data from reference tables and perform various calculations. It explains how to use VLOOKUP to find values, including total price and discount, based on matching codes. The script also covers how to format decimal discounts into percentages and calculates the total after applying discounts. Practical tips such as locking table references with F4 and using simple formulas for multiplication and subtraction are included to help users effectively apply these skills in their own work.

Takeaways

• 😀 VLOOKUP is a powerful function in Excel used to search for and retrieve data from a table based on a given value.
• 😀 The function requires three main components: lookup value, table array, and column index number.
• 😀 To make sure the table reference is locked, use absolute references (press F4) when defining the table array.
• 😀 The lookup value is typically a unique identifier (like a code) found in both the reference table and the main table.
• 😀 The column index in VLOOKUP determines which column from the reference table contains the data you want to retrieve.
• 😀 The 'range lookup' parameter should be set to 0 for an exact match when using VLOOKUP.
• 😀 After entering the VLOOKUP formula, you can drag it down to apply it to other rows of data.
• 😀 When calculating totals, you can multiply quantities by prices using simple formulas in Excel.
• 😀 To find discounts, use VLOOKUP again, but this time reference the column containing discount information in the reference table.
• 😀 If the discount is shown as a decimal, it should be converted to a percentage format to display it correctly.
• 😀 The final payment can be calculated by subtracting the discount (total × discount) from the total amount.

Q & A

What is the purpose of using the VLOOKUP function in the provided lesson?

The VLOOKUP function is used to search for a value in one table and retrieve corresponding information from another table, making it easier to perform calculations and reference data across tables.

What is the first step to using the VLOOKUP function?

The first step is to enter the VLOOKUP function in the desired cell and specify the lookup value, which is the code or indicator found in both the main table and the reference table.

How can you make the table array reference absolute in the VLOOKUP function?

To make the table array reference absolute, press F4 (or FN + F4 depending on your keyboard), which locks the reference, ensuring it does not change as you drag the formula down.

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

The column index number refers to the column in the reference table that contains the data you want to retrieve. For example, if the data is in the second column, the column index should be set to 2.

What does the range lookup value of 0 do in the VLOOKUP function?

Setting the range lookup value to 0 ensures that VLOOKUP performs an exact match lookup. It prevents the function from returning approximate matches.

What is the next step after applying the VLOOKUP function to retrieve data from the reference table?

After retrieving the data, you can perform calculations, such as multiplying quantity by price to calculate the total amount.

How can you calculate the discount based on the reference table?

To calculate the discount, use the VLOOKUP function again to retrieve the discount value from the reference table, ensuring the correct column index is used and the table array is absolute.

Why is it necessary to convert the discount value into a percentage?

The discount value retrieved from the reference table may be in decimal form. To display it as a percentage, you need to format the cell as a percentage or multiply the value by 100.

How do you calculate the total amount after applying the discount?

To calculate the total amount after the discount, subtract the discount (which is the discount percentage multiplied by the total) from the total amount.

Why is it important to lock the reference table using absolute references in the formula?

Locking the reference table with absolute references ensures that the VLOOKUP function always refers to the correct table and range, even when the formula is dragged to other cells.