Summary
The HLOOKUP function in Excel is used for horizontal lookup. It searches for a specified value in the top row of a table or range and returns a value in the same column from a row you specify. HLOOKUP is particularly useful for retrieving data organized in rows, where the lookup value is located in the first row. It can perform both approximate and exact matches.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value: The value to search for in the first row of the table_array.
- table_array: The range of cells containing the data to be searched.
- row_index_num: The row number in table_array from which to retrieve the value.
- [range_lookup]: [Optional] A logical value:
TRUEfor an approximate match orFALSEfor an exact match. If omitted,TRUEis the default.
Return value
Returns the corresponding value from the specified row, based on the match found in the first row of the table_array.
How to use
To use HLOOKUP, define the value to look up, specify the table array, and indicate which row to return the value from. Optionally, you can specify the match type – approximate or exact. Remember that HLOOKUP searches horizontally across the first row of the table_array.
Examples
HLOOKUP Approximate Match
Finding a Sales Level:
=HLOOKUP(C5, table, 2, TRUE)
In a sales table (named “table”), this formula finds the best matching sales level for the amount in C5, returning the value from the second row.
HLOOKUP Exact Match
Matching a Numeric Rating:
=HLOOKUP(C5, table, 2, FALSE)
In this example, the formula looks up a numeric rating (1-4) in C5 from a table named “table”, requiring an exact match, and returns the corresponding level from the second row.
HLOOKUP with Different Row Index
Retrieving Bonus Information:
=HLOOKUP(C5, table, 3, TRUE)
Similar to the first example, but this time it retrieves the bonus from the third row of the “table” based on the sales amount in C5.
HLOOKUP for Non-Numeric Data
Lookup for Non-Numeric Categories:
=HLOOKUP("Gold", table, 2, FALSE)
This formula looks up the category “Gold” in the first row of “table” and returns the associated value from the second row, requiring an exact match.
Additional Notes
- HLOOKUP is less flexible than VLOOKUP as it requires the lookup value to be in the first row of the table_array.
- For larger tables, consider using INDEX and MATCH as an alternative for more flexibility.
- HLOOKUP works best with data organized horizontally. For vertical data, use VLOOKUP.