What is the difference between HLOOKUP and VLOOKUP

The primary difference between HLOOKUP and VLOOKUP in Excel is the orientation of the lookup. Both functions are used to search for a value in a table and return a corresponding value from the same row or column, but they operate in different directions.

1. VLOOKUP (Vertical Lookup)

VLOOKUP stands for Vertical Lookup, meaning it searches for a value in the first column of a table and then retrieves a value from a specified column in the same row.

Syntax

excel

Copy code

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

• lookup_value: The value you want to search for in the first column.
• table_array: The range of data where the lookup occurs. The lookup value must be in the first column of this range.
• col_index_num: The column number (within the table_array) from which to return the value. For example, if you want to return a value from the third column, this would be 3.
• range_lookup (optional): Set to TRUE (or omitted) for an approximate match and FALSE for an exact match.

Example of VLOOKUP

Let’s say you have a table like this:

To find Mike’s age (lookup value = "Mike"), you would use:

excel

Copy code

=VLOOKUP("Mike", A2:C4, 3, FALSE)

• This searches for "Mike" in the first column (column A) and returns the value from the third column (C) in the same row, which is 22.

2. HLOOKUP (Horizontal Lookup)

HLOOKUP stands for Horizontal Lookup, meaning it searches for a value in the first row of a table and then retrieves a value from a specified row in the same column.

Syntax

excel

Copy code

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

• lookup_value: The value you want to search for in the first row.
• table_array: The range of data where the lookup occurs. The lookup value must be in the first row of this range.
• row_index_num: The row number (within the table_array) from which to return the value. For example, if you want to return a value from the second row, this would be 2.
• range_lookup (optional): Set to TRUE for an approximate match and FALSE for an exact match.

Example of HLOOKUP

Let’s say you have data structured like this:

To find the sales for the year 2019 (lookup value = "2019"), you would use:

excel

Copy code

=HLOOKUP(2019, A1:C2, 2, FALSE)

• This searches for 2019 in the first row (row 1) and returns the value from the second row (row 2) in the same column, which is 700.

Key Differences

Conclusion

• Use VLOOKUP when your data is organized vertically and you want to look up values in rows based on a value in the first column.
• Use HLOOKUP when your data is organized horizontally and you want to look up values in columns based on a value in the first row.