VLOOKUP() and HLOOKUP() in Advanced Excel

VLOOKUP() and HLOOKUP() are two powerful lookup functions in Excel that allow you to search for a value in a table and return a corresponding value from another column or row. These functions are very useful when you have large datasets and need to find specific information quickly.

VLOOKUP() Function

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. It is typically used when your data is organized vertically, with the search key in the leftmost column.

VLOOKUP() Syntax

Syntax:

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
  • lookup_value: The value you want to search for in the first column of the table.
  • table_array: The range of cells that contains the data you want to search.
  • col_index_num: The column number in the table from which to retrieve the value.
  • [range_lookup]: Optional. TRUE for an approximate match, or FALSE for an exact match. Default is TRUE.

Real-Time Example of VLOOKUP()

Scenario: You have a table of employee names and their corresponding departments and want to find the department of a specific employee.

        A               B
        1   Employee Name   Department
        2   John            HR
        3   Alice           Finance
        4   Mark            IT
        5   Emma            Marketing

Steps:

  1. In cell D2, enter the employee name you want to look up (e.g., "Alice").
  2. In cell E2, enter the following formula to find the department of the employee:
    3. =VLOOKUP(D2, A2:B5, 2, FALSE)
  3. Explanation: The formula searches for the value in D2 ("Alice") in the first column of the table (A2:A5) and returns the corresponding department from the second column (B2:B5). Since we want an exact match, we use FALSE.
  4. If you enter "Alice" in cell D2, the result in E2 will be "Finance".

HLOOKUP() Function

HLOOKUP stands for "Horizontal Lookup." It works similarly to VLOOKUP, but searches for a value in the first row of a table and returns a value from a specified row in the same column. It is typically used when your data is organized horizontally, with the search key in the topmost row.

HLOOKUP() Syntax

Syntax:

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
  • lookup_value: The value you want to search for in the first row of the table.
  • table_array: The range of cells that contains the data you want to search.
  • row_index_num: The row number in the table from which to retrieve the value.
  • [range_lookup]: Optional. TRUE for an approximate match, or FALSE for an exact match. Default is TRUE.

Real-Time Example of HLOOKUP()

Scenario: You have a table of product names and their sales for different months, and you want to find the sales of a specific product in a given month.

        A           B         C         D
        1   Product   Jan      Feb      Mar
        2   Product A 100      150      200
        3   Product B 120      130      180
        4   Product C 90       110      160

Steps:

  1. In cell F2, enter the product name you want to look up (e.g., "Product B").
  2. In cell F3, enter the month (e.g., "Feb").
  3. In cell F4, enter the following formula to find the sales for the specified product and month:
    4. =HLOOKUP(F3, B1:D4, MATCH(F2, A2:A4, 0), FALSE)
  4. Explanation: The formula first searches for the month name in F3 ("Feb") in the first row (B1:D1) and then uses the MATCH function to find the row number of the product name in F2 ("Product B") from the list in A2:A4. Finally, it returns the sales value from the intersecting row and column.
  5. If you enter "Product B" in cell F2 and "Feb" in cell F3, the result in F4 will be "130".

Key Differences Between VLOOKUP() and HLOOKUP()

  • VLOOKUP: Searches for the lookup value in the first column of a table and returns a value from a specified column in the same row.
  • HLOOKUP: Searches for the lookup value in the first row of a table and returns a value from a specified row in the same column.

Conclusion

Both VLOOKUP() and HLOOKUP() are extremely useful functions for quickly retrieving data from large datasets in Excel. VLOOKUP() is commonly used when the data is organized vertically, while HLOOKUP() is used when the data is organized horizontally. By using these functions, you can efficiently search for values and retrieve related information based on specific criteria, which can greatly improve your workflow in Excel.




Advertisement