(#1) Mudah Mencari Data dengan Fungsi Vlookup Hlookup, Analisis Data Informatika 8 Kurikulum Merdeka

Summary

In this educational video, Ana Ulia Esom introduces students to data search techniques using VLOOKUP and HLOOKUP functions in Excel, as part of the Informatics curriculum for 8th-grade students. She explains the difference between VLOOKUP (for vertical data lookup) and HLOOKUP (for horizontal data lookup), demonstrating how to search for city and province data based on input codes. Through practical examples and clear step-by-step instructions, viewers learn how to use these functions effectively to retrieve data in Excel. The video aims to simplify these functions for students and encourages engagement through likes and subscriptions.

Takeaways

• 😀 The video is an introduction to using data lookup functions in Excel, focusing on VLOOKUP and HLOOKUP for grade 8 students in the Indonesian curriculum.
• 😀 The script emphasizes the importance of learning data search techniques using functions like VLOOKUP and HLOOKUP for data analysis.
• 😀 VLOOKUP is used for searching data in vertical columns, while HLOOKUP is used for horizontal data search in rows.
• 😀 The VLOOKUP function includes four key components: lookup value, table array, column index number, and range lookup.
• 😀 The HLOOKUP function also consists of similar components: lookup value, table array, row index number, and range lookup.
• 😀 For both VLOOKUP and HLOOKUP, the range lookup can be set to TRUE for approximate matches or FALSE for exact matches.
• 😀 The tutorial walks through a practical example of using VLOOKUP to search for city data based on codes in a vertical table.
• 😀 In VLOOKUP, the lookup value is the code, the table array includes the code and city columns, and the column index number specifies which column to extract the data from.
• 😀 The HLOOKUP example searches for city data horizontally by adjusting the row index to find the correct city based on a code.
• 😀 The video also highlights how VLOOKUP and HLOOKUP return 'NA' if a value cannot be found in the table, indicating no match.

Q & A

What are the two main lookup functions explained in the video?

The two main lookup functions explained are VLOOKUP and HLOOKUP. VLOOKUP is used for vertical data arrangements, while HLOOKUP is used for horizontal data arrangements.

When should you use VLOOKUP instead of HLOOKUP?

VLOOKUP should be used when the data is arranged vertically (in columns), while HLOOKUP is used when the data is arranged horizontally (in rows).

What is the purpose of the 'lookup_value' in the VLOOKUP function?

The 'lookup_value' in VLOOKUP is the value you want to search for in the first column of the table to find related data in other columns.

What does the 'table_array' parameter in the VLOOKUP function represent?

The 'table_array' represents the range of cells that contain the data you want to search through, including both the column with the lookup value and the columns with the data to be retrieved.

What does the 'col_index_num' parameter do in the VLOOKUP function?

The 'col_index_num' specifies which column in the table array contains the data you want to retrieve. The numbering starts from 1 for the first column.

Explain the difference between the 'TRUE' and 'FALSE' values in the 'range_lookup' parameter.

In VLOOKUP, 'TRUE' is used for approximate matches, meaning the closest value to the lookup_value is returned. 'FALSE' is used for exact matches, meaning only values that exactly match the lookup_value will be returned.

What happens if VLOOKUP cannot find the lookup value in the table?

If VLOOKUP cannot find the lookup value in the table, it returns the error message '#N/A', indicating the value was not found.

How does HLOOKUP differ from VLOOKUP in terms of data arrangement?

HLOOKUP differs from VLOOKUP in that it searches for the lookup value in a horizontal row (from left to right), while VLOOKUP searches for the lookup value in a vertical column (from top to bottom).

What is the significance of 'row_index_num' in the HLOOKUP function?

The 'row_index_num' in HLOOKUP specifies which row in the table contains the data you want to retrieve. Row numbering starts from 1 for the first row.

Can you explain the structure of the HLOOKUP formula with an example?

The HLOOKUP formula follows the structure: '=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])'. For example, if you want to find a city from a horizontal table based on a code, you would use the formula '=HLOOKUP(lookup_value, table_array, 2, FALSE)', where the city data is in the second row of the table.