Vlookup Pada excel Pelajaran Spreadsheet Kelas X Akuntansi di SMK

Summary

This tutorial explains how to use the VLOOKUP function in Excel to efficiently read data from vertical tables. It covers how to set up data, name ranges for easier reference, and apply the VLOOKUP formula to find specific information like product names, prices, and discounts. The video guides users step-by-step, demonstrating how to lock data ranges for copying and adjusting formulas for precise results, including calculating prices and applying discounts. A final recap highlights how to use VLOOKUP for various data retrieval tasks, making it an essential tool for managing tabular data in Excel.

Takeaways

• 😀 VLOOKUP is a function in Excel used to search for data in a table vertically (by columns).
• 😀 When creating a table for VLOOKUP, avoid using spaces in table names; instead, use underscores.
• 😀 To define a table range, select the data and give it a meaningful name, like 'Kode_Barang'.
• 😀 The VLOOKUP formula is structured as =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]).
• 😀 'Lookup_value' is the value you want to search for in the first column of the table.
• 😀 'Table_array' refers to the range or named range where the data is located.
• 😀 'Col_index_num' is the column number from which you want to retrieve data. For example, if you're looking for product names, it's usually column 2.
• 😀 To ensure an exact match, set the 'range_lookup' parameter to 'FALSE'.
• 😀 You can apply the VLOOKUP function for various types of data, including names, prices, and discounts.
• 😀 VLOOKUP can also be used to calculate totals by multiplying the lookup result with quantities or prices.
• 😀 When applying VLOOKUP for discounts, you can retrieve percentage or currency values and use them for further calculations.

Q & A

What is the VLOOKUP function used for in Excel?

The VLOOKUP function is used to search for a value in the first column of a table and return a corresponding value from another column in the same row, essentially reading data vertically.

How do you start using the VLOOKUP function in Excel?

To start using the VLOOKUP function, you first need to select the data table and assign a name to it (e.g., 'Kode Barang') to make referencing easier and more organized.

What is important when naming a table in Excel for use with VLOOKUP?

When naming a table for use with VLOOKUP, it's important to avoid using spaces in the table name. You can use underscores or other valid characters instead.

How do you determine which column to reference for the result in VLOOKUP?

You determine which column to reference by counting the columns in your table. For example, if you're looking for 'Nama Barang', and it's in the second column, you'll specify column 2 in your VLOOKUP function.

What does the 'column index' refer to in the VLOOKUP function?

The 'column index' refers to the position of the column from which you want to return the result. For example, the second column in the table has a column index of 2.

What does the 'range_lookup' argument in VLOOKUP do?

The 'range_lookup' argument specifies whether you want an exact match (FALSE) or an approximate match (TRUE). In this script, the value '0' is used to indicate an exact match, equivalent to FALSE.

Can you perform VLOOKUP without naming the table?

Yes, you can use VLOOKUP without naming the table. Instead, you select the range of data directly, and then use the F4 key to lock the data range so it doesn't change when you copy the formula.

What does the F4 key do in the context of the VLOOKUP function?

Pressing the F4 key in Excel locks the data range, which means it fixes the reference and prevents the range from shifting when the formula is copied to other cells.

How can you calculate the price of an item in Excel using VLOOKUP?

To calculate the price, you can multiply the quantity by the price from the table using the VLOOKUP function to pull the correct price data for each item.

How can you calculate the discount in Excel using VLOOKUP?

To calculate the discount, use VLOOKUP to retrieve the discount rate from the table, then apply it to the price. You can either display the discount as a percentage or convert it to a currency value by multiplying it with the total price.