Contoh Soal 1 Test Excel Fungsi Gabungan Vlookup Hlookup dan IF

Summary

This Excel tutorial focuses on solving a test question involving key Excel functions such as VLOOKUP, HLOOKUP, IF, and text manipulation functions (LEFT, RIGHT, MID). The video demonstrates how to apply these functions to fill missing data based on tables containing class codes, status codes, and prices. Viewers are guided step-by-step on using Name Boxes for easy table referencing, creating complex formulas to extract values, and calculating totals. The tutorial offers practical insights into Excel functions and highlights common pitfalls, making it a useful resource for learners seeking to improve their spreadsheet skills.

Takeaways

• 😀 Understanding the basic Excel functions like LEFT, RIGHT, MID, IF, VLOOKUP, and HLOOKUP is crucial for solving this problem.
• 😀 LEFT, RIGHT, and MID functions are used to extract specific portions of text from strings, with LEFT and RIGHT focusing on the beginning and end, respectively, and MID extracting from the middle.
• 😀 The IF function is essential for conditional logic in Excel, allowing users to return different values based on whether a condition is true or false.
• 😀 VLOOKUP is used to search vertically in a table, while HLOOKUP searches horizontally—these two functions are key in retrieving data from structured tables.
• 😀 The use of named ranges (e.g., TB_Kelas, TB_Status) makes formula management easier and more intuitive in Excel.
• 😀 The example provided demonstrates how to use the HLOOKUP function with the LEFT function to extract class codes and match them to the correct class names.
• 😀 Similarly, VLOOKUP with the RIGHT function allows for extracting status codes and mapping them to corresponding status names.
• 😀 Combining VLOOKUP with IF enables the handling of dynamic pricing calculations based on status codes (e.g., Economy or Business).
• 😀 The final calculation in column I involves multiplying the price from column G by a given rate in H2 to get the total price.
• 😀 Excel formulas such as HLOOKUP, VLOOKUP, and IF can be dragged down to apply them across multiple rows efficiently, saving time and reducing errors.
• 😀 Formatting and neatness, such as using the Increase Indent feature, is important for improving the readability and organization of Excel worksheets.

Q & A

What is the purpose of the video script?

The purpose of the video script is to explain how to use Excel functions, specifically a combination of vertical and horizontal lookups, along with the IF function, to solve problems related to filling out tables with class data, status, and pricing.

What Excel functions are being taught in this video?

The video teaches the use of the VLOOKUP, HLOOKUP, and IF functions in Excel, along with text manipulation functions like LEFT, RIGHT, and MID.

How are the VLOOKUP and HLOOKUP functions different?

VLOOKUP is used for vertical lookups, meaning it searches data in columns, while HLOOKUP is used for horizontal lookups, meaning it searches data in rows.

What is the significance of the 'name box' in Excel?

The 'name box' is used to assign a name to a range of cells or a table, which simplifies the process of referencing these ranges in formulas. It helps users easily identify and access the data.

What is the role of the 'FALSE' or '0' index in VLOOKUP or HLOOKUP functions?

The 'FALSE' or '0' index in VLOOKUP and HLOOKUP functions is used to specify an exact match lookup. It ensures that the function only returns a result if an exact match is found.

How is the LEFT function used in this tutorial?

The LEFT function is used to extract a specific number of characters from the beginning of a text string. In this tutorial, it is used to extract the class code (e.g., 'EX' for Executive) from a cell.

What does the IF function do in the context of the pricing formula?

The IF function in the pricing formula checks the class code (like 'EKA' for Economy) and assigns a corresponding price value based on the result of the logical test. It helps to determine the price based on certain conditions.

Why is the MID function mentioned in the video, and how is it different from LEFT and RIGHT?

The MID function is mentioned as an alternative to LEFT and RIGHT. It allows the user to extract a portion of a text string starting from a specific position, rather than from the beginning or end. This function provides more flexibility in text manipulation.

How does the formula in G4 combine VLOOKUP and IF functions?

In G4, the formula combines VLOOKUP and IF functions to lookup the class code (using VLOOKUP) and then apply a conditional check (using IF) to determine the correct price based on the class type, such as 'EKA' for Economy.

What does the final formula in G4 do when multiplied by the value in H2?

The final formula in G4 multiplies the result of the VLOOKUP and IF function by the value in H2, which represents the price modifier or additional multiplier. This gives the final price for the selected class.