WAJIB BISA! 15 RUMUS EXCEL UNTUK TES ADMIN DAN SELEKSI KERJA (2021)

Summary

In this tutorial, the speaker explains Excel formulas, focusing on `VLOOKUP` and `HLOOKUP`. They guide viewers through using these formulas for data lookup, specifically for delivery times based on order numbers and city names. The tutorial is practical, offering step-by-step instructions to help those preparing for admin or job selection tests. The video highlights the difference between vertical and horizontal lookups, ensuring viewers can apply the formulas correctly. The speaker encourages interaction through likes, comments, and subscriptions, providing a valuable resource for anyone needing Excel knowledge.

Takeaways

• 😀 HLOOKUP and VLOOKUP are important Excel formulas for looking up data from tables, but they differ in how they search for the data.
• 😀 VLOOKUP searches vertically within a table by column, while HLOOKUP searches horizontally by row.
• 😀 For VLOOKUP, the lookup value must be in the leftmost column, and the result is returned from the right.
• 😀 For HLOOKUP, the lookup value must be in the top row of the table, and the result is returned from a lower row.
• 😀 The tutorial emphasizes the importance of correctly using absolute references (F4 key) in Excel formulas to lock table ranges.
• 😀 The tutorial demonstrates how to use HLOOKUP with city names to return delivery times based on the city of residence.
• 😀 The speaker uses cities like Jakarta, Bandung, Palembang, and Surabaya to explain delivery times, which vary based on location.
• 😀 The speaker explains that formulas like HLOOKUP can be used in administrative exams or work tests that require Excel skills.
• 😀 The tutorial encourages viewers to like, comment, and subscribe to the channel for more helpful content on Excel formulas.
• 😀 The speaker invites viewers to share the video with friends who may benefit from the tutorial and to provide feedback on future topics.

Q & A

What is the main focus of the video tutorial?

The tutorial primarily focuses on teaching how to use Excel formulas like `VLOOKUP` and `HLOOKUP` to find specific information from a dataset, particularly regarding shipping durations based on cities or order numbers.

What is the difference between `VLOOKUP` and `HLOOKUP`?

`VLOOKUP` is used when the data is arranged vertically, while `HLOOKUP` is used when the data is arranged horizontally. The main difference is that `VLOOKUP` searches in columns, and `HLOOKUP` searches in rows.

Why is it important to use the `F4` key when referencing data in Excel formulas?

The `F4` key locks the reference in the formula, ensuring that when you drag the formula to other cells, the reference stays fixed and does not change. This is essential for accuracy in calculations, especially when working with large datasets.

What kind of dataset is used in the example for the tutorial?

The dataset used in the example involves shipping durations for different cities (Jakarta, Bandung, Palembang, Surabaya). The goal is to retrieve the shipping time for each city using either `VLOOKUP` or `HLOOKUP`.

What data does the user need to input to find the shipping duration?

The user needs to input the city or domicile (such as Jakarta, Bandung, Palembang, or Surabaya) as the search value. The formula will then return the shipping duration corresponding to that city.

In the `HLOOKUP` example, what does the second row represent?

In the `HLOOKUP` example, the second row contains the shipping durations, which is the data that the formula retrieves based on the city selected in the search.

How does the user apply the `HLOOKUP` formula in the example?

The user applies the `HLOOKUP` formula by first selecting the city (the search value), then referencing the data table using the `HLOOKUP` formula to retrieve the shipping duration from the second row of the table.

What happens if the `F4` key is not used when referencing the table in Excel?

If the `F4` key is not used, the table reference will not be locked, and dragging the formula across cells might cause the reference to shift, resulting in incorrect or inaccurate results.

How can the user check the shipping duration for a city after applying the `HLOOKUP` formula?

After applying the `HLOOKUP` formula, the user can check the shipping duration by looking at the result that appears next to the city name, which corresponds to the number of days for shipping.

Why does the speaker recommend watching the video for people preparing for admin tests or work tests?

The speaker recommends the video for those preparing for admin or work-related tests because understanding Excel formulas like `VLOOKUP` and `HLOOKUP` is often essential in administrative tasks, especially when dealing with data analysis or inventory management.