VLOOKUP & HLOOKUP Excel: Cari Nilai Siswa dengan NIS | Tutorial Lengkap

Summary

In this tutorial, the presenter explains the use of VLOOKUP and HLOOKUP functions in Excel, focusing on how to extract data based on specific identifiers such as student IDs (NIS). The video demonstrates how to set up vertical and horizontal lookup tables to fetch corresponding data like student names, classes, and scores. The tutorial also covers practical examples, including creating a basic report card using these functions in combination with SUM and AVERAGE functions. Additionally, tips like using data validation lists for easier input and ensuring accurate results with the FALSE argument in lookups are discussed.

Takeaways

• 😀 VLOOKUP and HLOOKUP functions in Excel are used to retrieve data from a table based on specific identifiers such as student IDs or NIS numbers.
• 😀 VLOOKUP works vertically, meaning it searches for data within columns, while HLOOKUP works horizontally, searching within rows.
• 😀 The VLOOKUP function requires the user to specify a lookup value, table array, column index, and an optional 'false' or 'true' argument for exact or approximate matching.
• 😀 In VLOOKUP, the 'column index' refers to the number of the column from which data is being retrieved, with 1 being the first column of the table.
• 😀 The 'false' argument in VLOOKUP ensures that only exact matches are returned, preventing errors when using approximate values.
• 😀 To use VLOOKUP with a list of values instead of typing them manually, data validation with a list can be applied, making data entry easier.
• 😀 HLOOKUP functions similarly to VLOOKUP but is used for horizontal searches in a table where data is organized in rows.
• 😀 In HLOOKUP, the 'row index' is used instead of 'column index' to specify which row's data is to be retrieved.
• 😀 For better efficiency when copying formulas in Excel, the table array can be made absolute by pressing F4, ensuring it doesn't shift when copying the formula to other cells.
• 😀 Combining VLOOKUP or HLOOKUP with basic functions like SUM and AVERAGE can be useful for tasks such as calculating totals and averages in educational reports like gradebooks.
• 😀 Excel formulas like VLOOKUP and HLOOKUP become especially valuable in automated reporting, such as generating school report cards, by dynamically pulling student data from reference tables.

Q & A

What is the purpose of using VLOOKUP and HLOOKUP in Excel?

VLOOKUP and HLOOKUP are used to retrieve data from a table based on a reference value. VLOOKUP works vertically (searches columns), while HLOOKUP works horizontally (searches rows). Both are used for looking up and displaying related data, such as student names, grades, or item prices, based on a unique identifier like NIS or an item name.

How does VLOOKUP work in Excel?

VLOOKUP in Excel is used to search for a value in the first column of a table and return a value in the same row from another column. The syntax is `=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`. It helps retrieve data like names or grades based on a reference value such as NIS.

What is the difference between VLOOKUP and HLOOKUP?

The main difference is the direction of the lookup. VLOOKUP searches for data vertically, across columns, whereas HLOOKUP searches for data horizontally, across rows. Both functions retrieve data based on a reference value, but they are applied to different data layouts.

What does the 'FALSE' argument do in the VLOOKUP and HLOOKUP functions?

The 'FALSE' argument in VLOOKUP and HLOOKUP ensures that the function returns an exact match for the lookup value. If 'TRUE' is used or omitted, the function may return an approximate match, which can lead to inaccurate results if the data is not sorted.

Why is it important to use absolute references in Excel formulas?

Absolute references (using `$` or pressing F4) in Excel are important because they allow specific cell references to stay fixed when copying formulas to other cells. Without absolute references, the cell references would change dynamically, which could lead to incorrect calculations.

How do you apply data validation for a dropdown list in Excel?

To apply data validation for a dropdown list in Excel, select the cell where you want the dropdown, go to the **Data** tab, choose **Data Validation**, and select **List**. Then, specify the range of values to be included in the dropdown list. This allows you to select from predefined options instead of typing values manually.

What are the steps to use the SUM function in Excel?

The SUM function in Excel adds up a range of numbers. To use it, enter `=SUM(range)` where 'range' refers to the cells you want to sum. For example, `=SUM(B2:B10)` will sum all the values from cells B2 to B10.

How do you calculate an average in Excel using the AVERAGE function?

To calculate an average in Excel, use the `AVERAGE` function. The syntax is `=AVERAGE(range)`, where 'range' is the set of values you want to average. For example, `=AVERAGE(C2:C10)` will compute the average of values from C2 to C10.

How do you fix a formula when the lookup value changes in a copied formula?

To fix a formula when the lookup value changes in a copied formula, use absolute references. Press F4 to lock the cell reference, so when you copy the formula to other cells, the reference stays fixed and doesn't shift.

What is the function of the 'FALSE' argument in VLOOKUP and HLOOKUP when performing lookups?

The 'FALSE' argument ensures that the lookup function returns an exact match. Without it, the function might return an approximate match, which can cause errors when the data is not sorted or if the lookup value doesn't precisely match any entry.