Cara Menggunakan Rumus VLOOKUP dan HLOOKUP di Excel | Tutorial Excel

Introduction

In this tutorial, we will explore how to use the VLOOKUP and HLOOKUP functions in Excel. These powerful lookup functions help you search for specific data within tables, making data management and analysis much easier. Whether your reference table is on the same sheet or a different one, this guide will provide clear, actionable steps to use these functions effectively.

Step 1: Understanding VLOOKUP and HLOOKUP

• VLOOKUP: Stands for "Vertical Lookup." It searches for a value in the first column of a table and returns a value in the same row from a specified column.
• HLOOKUP: Stands for "Horizontal Lookup." It searches for a value in the first row of a table and returns a value in the same column from a specified row.

When to Use Each Function

• Use VLOOKUP for vertical data arrangements.
• Use HLOOKUP for horizontal data arrangements.

Step 2: Setting Up Your Data

1. Create Your Dataset:

   • Organize your data in a table format.
   • Ensure your lookup values are in the first column (for VLOOKUP) or first row (for HLOOKUP).

2. Example Data:

   • Suppose you have a table with product IDs in column A and their corresponding prices in column B.

Step 3: Using VLOOKUP

1. Select the Cell for the Formula: Click on the cell where you want the result of the VLOOKUP to appear.

2. Enter the VLOOKUP Formula:

   • The basic syntax is:

     =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
     

   • Parameters:
     • lookup_value: The value you want to search for (e.g., a product ID).
     • table_array: The range of cells containing your data (e.g., A1:B10).
     • col_index_num: The column number in the table from which to retrieve the value (e.g., 2 for prices).
     • [range_lookup]: TRUE for approximate match or FALSE for an exact match.

3. Example Formula:

   =VLOOKUP(A2, A1:B10, 2, FALSE)
   

   • This formula looks up the value in cell A2 and returns the corresponding price from column B.

Step 4: Using HLOOKUP

1. Select the Cell for the Formula: Click on the cell where you want the result of the HLOOKUP to appear.

2. Enter the HLOOKUP Formula:

   • The basic syntax is:

     =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
     

   • Parameters:
     • lookup_value: The value you want to search for (e.g., a header).
     • table_array: The range of cells containing your data (e.g., A1:D2).
     • row_index_num: The row number from which to retrieve the value (e.g., 2 for data in the second row).
     • [range_lookup]: TRUE for approximate match or FALSE for an exact match.

3. Example Formula:

   =HLOOKUP(B1, A1:D2, 2, FALSE)
   

   • This formula looks up the value in cell B1 and returns the corresponding data from the second row.

Step 5: Referencing Different Sheets

• You can use VLOOKUP or HLOOKUP across different sheets.
• Use the following format for the table_array parameter:
  • For VLOOKUP:

    =VLOOKUP(A2, 'Sheet2'!A1:B10, 2, FALSE)
    

  • For HLOOKUP:

    =HLOOKUP(B1, 'Sheet2'!A1:D2, 2, FALSE)
    

Conclusion

By following these steps, you can effectively use the VLOOKUP and HLOOKUP functions in Excel to streamline your data analysis tasks. Remember to ensure your data is well-organized for optimal results. For further practice, download the Excel file used in the tutorial from the provided link and try creating your own lookup formulas. Happy Excelling!