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.