VLOOKUP vs HLOOKUP in Excel: Key Differences and Usage

VLOOKUP searches for a value vertically in the first column of a table and returns a value from a specified column. HLOOKUP works similarly but searches horizontally in the first row and returns a value from a specified row.

Quick Comparison

Here is a quick side-by-side comparison of VLOOKUP and HLOOKUP functions in Excel.

Feature	VLOOKUP	HLOOKUP
Lookup Direction	Vertical (searches first column)	Horizontal (searches first row)
Return Value From	Specified column in the same row	Specified row in the same column
Common Use Case	Find data in vertical lists or tables	Find data in horizontal tables or headers
Syntax	VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])	HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Data Layout Requirement	Lookup values must be in the first column	Lookup values must be in the first row

Key Differences

VLOOKUP and HLOOKUP are both lookup functions but differ mainly in the direction they search. VLOOKUP looks down the first column of a range to find a match, then returns a value from a column you specify in the same row. This makes it ideal for vertical tables where data is organized in columns.

On the other hand, HLOOKUP searches across the first row of a range to find a match, then returns a value from a row you specify in the same column. This suits horizontal tables where data is arranged in rows.

Both functions require the lookup value to be in the first column (for VLOOKUP) or first row (for HLOOKUP) of the table array. They also share similar syntax but differ in the index argument: col_index_num for VLOOKUP and row_index_num for HLOOKUP.

Code Comparison

Example: Find the price of "Apple" in a vertical product list using VLOOKUP.

excel

=VLOOKUP("Apple", A2:B5, 2, FALSE)

Output

1.20

HLOOKUP Equivalent

Example: Find the price of "Apple" in a horizontal product list using HLOOKUP.

excel

=HLOOKUP("Apple", A1:D2, 2, FALSE)

Output

1.20

When to Use Which

Choose VLOOKUP when your data is organized vertically with lookup values in the first column. It is perfect for lists and tables where each row represents a record.

Choose HLOOKUP when your data is arranged horizontally with lookup values in the first row. This is useful for tables where each column represents a record or category.

In general, VLOOKUP is more commonly used because vertical data layouts are more frequent in spreadsheets.

Key Takeaways

VLOOKUP searches vertically in the first column; HLOOKUP searches horizontally in the first row.

Use VLOOKUP for vertical tables and HLOOKUP for horizontal tables.

Both require the lookup value to be in the first column or row of the table array.

Syntax differs only in the index argument: column index for VLOOKUP, row index for HLOOKUP.

VLOOKUP is more common due to typical vertical data layouts.