HLOOKUP
The HLOOKUP function returns a value from a collection of rows by using the top row of values to pick a column and a row number to pick a row within that column.
Tip: For an enhanced search that works in any direction, use XLOOKUP.
HLOOKUP(search-for, rows-range, return-row, close-match)
rows-range: A collection of cells. rows-range must be a reference to a single range of cells, which may contain any values.
return-row: A number value representing the row number from which to return the value. return-row must be greater than or equal to 1 and less than or equal to the number of rows in the specified range.
close-match: An optional modal value that specifies whether an exact match is required.
close match (TRUE, 1 or omitted): If there’s no exact match, select the column with the largest top-row value that is less than or equal to the search value. If you choose close match, search-for can’t contain wild cards.
exact match (FALSE or 0): If there’s no exact match, return an error. If you choose exact match, search-for can contain wild cards. You can use a ? (question mark) to represent one character, an * (asterisk) to represent multiple characters, and a ~ (tilde) to specify that the following character should be matched rather than used as a wild card. REGEX can only be used in HLOOKUP if you use exact match.
Notes
HLOOKUP compares a search value to the values in the top row of a specified collection. Unless an exact match is required, the column containing the largest top-row value that is less than or equal to the search value is selected. Then, the value from the specified row in that column is returned by the function. If an exact match is required and none of the top-row values match the search value, the function returns an error.
Examples |
|---|
Given the following table: |
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | 0 | 20 | 40 | 60 | 80 |
2 | A | E | I | O | U |
3 | vel | elit | dolor | sit | amet |
4 | 1 | 2 | 3 | 4 | 5 |
=HLOOKUP(20, A1:E4, 2) returns "E", because the value "E" is in the same column as 20 (search-for), in the second row (return-row) within the collection specified (rows-range). =HLOOKUP(39, A1:E4, 2) returns "E", because "E" is in the same column as 20, the closest match that does not exceed the value given (39), in the second row of the collection specified. =HLOOKUP("M", A2:E4, 2) returns "dolor", because "dolor" is in the same column as "I", the closest match that does not exceed the value given ("M"), in the second row of the collection specified (which is row 3 because the first row specified was 2). =HLOOKUP("C", A2:E3, 2) returns "vel", because "vel" is in the same column as "A", the closest match that does not exceed the value given ("C"), in the second row of the collection specified. =HLOOKUP("blandit", A3:E4, 2) returns 5, because 5 is in the same column as "amet", which is the last value in the specified ranges. This is the case because "blandit" wasn't found in the second row of the specified collection. =HLOOKUP("C", A2:E4, 3, TRUE) returns 1, because 1 is in the same column as "A", the closest match that does not exceed the value given ("C"), in the third row of the collection specified. =HLOOKUP("C", A2:E4, 3, FALSE) returns an error that the requested value couldn’t be found because the value "C" can’t be found (there is no exact match) and exact-match was specified as FALSE. =HLOOKUP("C", A2:E4, 4, TRUE) returns an invalid reference error because there is no fourth row in the collection specified (which is only three rows). =HLOOKUP(REGEX("^a.*"), A3:E4, 2, FALSE) returns 5, because 5 is in the same column as "amet," the first word in A3:E3 starting with an "a". |