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.
HLOOKUP(search-for, rows-range, return-row, close-match)
search-for: The value to find. search-for can contain any value.
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.
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”, the last value in the specified ranges because “blandit” was not found, in the second row of the collection specified. =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). |