HLOOKUP

HLOOKUP(lookup_value, table_array, row_index_num [,range_lookup])

Returns the value in the same column after finding a matching value in the first row.

lookup_value	The value you want to find in the first row of the table.
table_array	The cell range or range name containing the table of data.
row_index_num	The row number for the value you want to return.
range_lookup	(Optional) A logical value indicating the type of match: True (<> 0) Closest match, exact or closest value less than it (default) False (= 0) Exact match, the first row does not have to be sorted

REMARKS

* XLOOKUP was added in Excel 2021 to replace this function.
* For an illustrated example refer to the page under Lookup Functions
* This function is not case sensitive when searching for text strings.
* This function supports wildcards (? and *).
* Wildcard character: ? = a single character.
* Wildcard character: * = multiple characters.
* To include the actual wildcard characters use a tilde prefix (~?), (~*) and (~~).
* This function cannot return values that are above the lookup row.
* The 'lookup_value' can be a number, text, logical value, or a name or reference that refers to one of these.
* If 'lookup_value' cannot be found and 'range_lookup' is False, then #N/A is returned.
* If 'lookup_value' cannot be found and 'range_lookup' is True, the value used by this function can be confusing.
* If 'lookup_value' is less than the smallest value, then #N/A is returned.
* If 'lookup_value' is between the smallest and largest values, the closest value less than it is used.
* If 'lookup_value' is greater than the largest value, then the largest value is used (which is the closest value less than it).
* If "table_array" contains duplicate values, then the results are based on the first match found.
* If "row_index_num" = 1, the first row value in "table_array" is used.
* If "row_index_num" = 2, the second row value in "table_array" is used.
* If "row_index_num" < 1, then #VALUE! is returned.
* If "row_index_num" > the number of rows in "table_array", then #REF! is returned.
* If 'row_index_num' is not numeric, then #VALUE! is returned.
* If 'range_lookup' = True, then the first row must be sorted into ascending order (-1, 0, 1, a, A, FALSE, TRUE).
* This function will only return values less than 256 characters long. If the matching value is more than 255 characters, then #N/A is returned.
* You can use the XMATCH function to return the position of a value.
* You can use the VLOOKUP function to return the value in the same row.
* You can use the LOOKUP function to return a value in a row (or column) that matches a value in a column (or row).
* You can use the INDEX function to return the intersection of a row and a column.
* You can use the MATCH function to return the position of a value.
* You can use the INDEX and MATCH combination as an alternative when your 'lookup value' is not in the first row.
* You can use the IFNA function to return the value or something else if it evaluates to #N/A.
* This function has been available since Excel 1997.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C	D
1	=HLOOKUP("Tokyo", B1:D2, 2, FALSE) = 6	Paris	Rome	Tokyo
2	=HLOOKUP("Paris", B1:D2, 2, FALSE) = 8	8	11	6
3	=HLOOKUP("Rome", B1:D2, 2, FALSE) = 11
4	=HLOOKUP("NoMatch", B1:D2, 2, FALSE) = #N/A	Tokyo	Rome	Paris
5	=HLOOKUP("Rom", B1:D2, 1, TRUE) = Paris	6	11	8
6	=HLOOKUP("Romx", B1:D2, 1, TRUE) = Rome
7	=HLOOKUP("Tok", B1:D2, 1, TRUE) = Rome
8	=HLOOKUP("Par", B1:D2, 1, TRUE) = #N/A
9	=HLOOKUP("Toky", B4:D5, 1, TRUE) = Paris
10	=HLOOKUP("Rom", B4:D5, 1, TRUE) = #N/A
11	=HLOOKUP("Wrong", B4:D5, 1, TRUE) = Paris

1 - Return the value in the 2nd row of "B1:D2" from the column that matches "Tokyo" in the 1st row.
2 - Return the value in the 2nd row of "B1:D2" from the column that matches "Paris" in the 1st row.
3 - Return the value in the 2nd row of "B1:D2" from the column that matches "Rome" in the 1st row.
4 - Return the value in the 2nd row of "B1:D2" from the column that matches "NoMatch" in the 1st row. There is no match.
5 - Return the value in the 1st row of "B1:D2" from the column that has the closest match to "Rom" in the 1st row.
The 1st row is sorted. The value to the left of "Rom" is "Paris".
6 - Return the value in the 1st row of "B1:D2" from the column that has the closest match to "Romx" in the 1st row.
The 1st row is sorted. The value to the left of "Romx" is "Paris".
7 - Return the value in the 1st row of "B1:D2" from the column that has the closest match to "Tok" in the 1st row.
The 1st row is sorted. The value to the left of "Tok" is "Rome".
8 - Return the value in the 1st row of "B1:D2" from the column that has the closest match to "Par" in the 1st row.
The value "Par" is smaller than the smallest value.
9 - Return the value in the 1st row of "B4:D5" from the column that has the closest match to "Toky" in the 1st row.
Incorrect. The 1st row is not sorted. The value "Toky" is smaller than the smallest value, so returning "Paris" is very confusing.
10 - Return the value in the 1st row of "B4:D5" from the column that has the closest match to "Rom" in the 1st row.
Incorrect. The 1st row is not sorted. The value above "Rom" is "Tokyo" so returning #N/A is very confusing.
11 - Return the value in the 1st row of "B4:D5" from the column that has the closest match to "Wrong" in the 1st row.
Incorrect. The value "Wrong" is larger than the last column so returning "Paris" seems to be correct which is very confusing.