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 the next smallest (default) False (= 0) Exact match, the first row does not have to be sorted |
REMARKS |
* XLOOKUP was added in Microsoft 365 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, then the next smallest value is returned. * If "lookup_value" is smaller than the smallest value, then #N/A is returned. * 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 |
|
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. |
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top