The HLOOKUP function in Excel is used to search for a value in the top row of a table or range and return a value in the same column from a row you specify. It is especially useful when your data is arranged horizontally. Here is how you can use the HLOOKUP function:
Syntax:
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Parameters:
- lookup_value: The value you want to search for in the top row of the table or range.
- table_array: The range that contains the data. The top row of this range is where the HLOOKUP function will search for the lookup value.
- row_index_num: The row number in the table_array from which to retrieve the value. The top row is 1, the second row is 2, and so on.
- range_lookup (optional): A logical value that specifies whether you want an exact match or an approximate match. Use `TRUE` for an approximate match or `FALSE` for an exact match. If omitted, the default is `TRUE`.
Example:
Suppose you have a table where the top row contains product IDs, and the second row contains product prices:
| | A | B | C | D |
|——-|———|———|———|———|
| 1 | ID101 | ID102 | ID103 | ID104 |
| 2 | $10 | $15 | $20 | $25 |
To find the price associated with `ID103`, you can use the HLOOKUP function as follows:
=HLOOKUP("ID103", A1:D2, 2, FALSE)
Breakdown:
- “ID103”: This is the value you are looking up in the top row.
- A1:D2: This is the range that contains the table.
- 2: This indicates that you want to retrieve the value from the second row of the table_array.
- FALSE: This ensures that the function looks for an exact match.
Tips:
- Ensure that `lookup_value` is present in the top row of your specified `table_array`, otherwise, the function will return an error.
- If `range_lookup` is set to `TRUE` or is omitted, make sure that the data in the top row is sorted in ascending order; otherwise, HLOOKUP might return incorrect results if an exact match is not found.
- Use `FALSE` for `range_lookup` if you require an exact match.
That’s how you can use the HLOOKUP function to fetch data from horizontally arranged tables in Excel.
Unlock Your Potential
Excel
Access
Power BI
