HLOOKUP
The HLOOKUP function searches for a value in the first row of a range and returns the corresponding value from the same column and a specified row.
This function can be useful for searching data in large spreadsheets.
Syntax
HLOOKUP(lookup_value, range, row_num, [lookup_method])
| Argument | Description | Permitted values |
|---|---|---|
| lookup_value | Value to search for in the first row of the range | Any value (number, text, and so forth) |
| range | Range in which the search needs to be carried out | Any range or array of cells |
| row_num | Number of the row containing the search value. First row in the range corresponds to 1 | Integer grater than 0 |
| [lookup_method] | (optional) Boolean value that determines the search type: approximate (TRUE) or exact (FALSE). By default, TRUE | TRUE or FALSE |
Example of use
Suppose you have a spreadsheet with data on product sales by quarter:
| A | B | C | D | E | |
| 1 | Item | Q1 | Q2 | Q3 | Q4 |
| 2 | Apples | 120 | 150 | 130 | 110 |
| 3 | Pears | 80 | 90 | 95 | 100 |
| 4 | Oranges | 200 | 180 | 220 | 210 |
If you want to find sales of pears for the 3rd quarter, you can use the HLOOKUP function as follows:
=HLOOKUP("Q3", A1:E4, 3, FALSE)
Explanation of function argument values
- “Q3”: The value you are searching for (the name of the quarter in the first row).
- A1:E4: The range in which the search is performed (includes rows with item names and quarters).
- 3: The row number from which to return the value. We are looking for “Pears” in the third row of the range A1:E4.
- FALSE: Indicates that an exact match needs to be found.
Result
The function will return the value 95, which corresponds to pear sales in the 3rd quarter.
Notes
- For an exact search, use FALSE or 0 in the fourth argument.
- The first row of the range must contain the values to be searched for.
- The HLOOKUP function is similar to the VLOOKUP function, but is designed for spreadsheets with a horizontal data structure.
- If the value is not found, the #N/A error is returned.