Both VLOOKUP and HLOOKUP are used to search for a value in a dataset and return a corresponding value from another column or row.
Searches for a value in the first column of a table and returns a value from a specified column in the same row.
Works when data is arranged in vertical columns.
Syntax:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments:
lookup_value: The value to search for.table_array: The range containing data.col_index_num: The column number from which to return the result.[range_lookup]: TRUE (approximate match) or FALSE (exact match). Default is TRUE.Example:
Suppose we have a table in A2:C5:
| ID | Name | Score |
|---|---|---|
| 101 | John | 85 |
| 102 | Alice | 92 |
| 103 | Bob | 78 |
Formula:
=VLOOKUP(102, A2:C5, 2, FALSE)
Result: "Alice" (Finds 102 in column A and returns Name from column 2).
Searches for a value in the first row of a table and returns a value from a specified row in the same column.
Works when data is arranged in horizontal rows.
Syntax:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Arguments:
lookup_value: The value to search for.table_array: The range containing data.row_index_num: The row number from which to return the result.[range_lookup]: TRUE (approximate match) or FALSE (exact match).Example:
Suppose we have data in A1:E2:
| ID | 101 | 102 | 103 | 104 |
|---|---|---|---|---|
| Score | 85 | 92 | 78 | 88 |
Formula:
=HLOOKUP(103, A1:E2, 2, FALSE)
Result: 78 (Finds 103 in row 1 and returns Score from row 2).
| Feature | VLOOKUP | HLOOKUP |
|---|---|---|
| Searches in | First column | First row |
| Works on | Vertical data | Horizontal data |
| Returns from | A specified column | A specified row |
| Example Use | Find price of a product | Find sales by month |