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_valueThe value to look for in the first row of the table.
table_arrayThe cell range or range name containing the table of data.
row_index_numThe row number for the value you want to return.
range_lookup(Optional) A logical value specifying the type of match performed:
True / 1 = Closest match, exact or the next smallest (table_array must be sorted in ascending order) (default)
False / 0 = Exact match (table_array 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.
* 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" = 1 or True, then a closest match is returned. In other words, if an exact match is not found, the next smallest value is returned.
* If "range_lookup" = 1 or 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 XLOOKUP function to return a value in the same row (or column).
* 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 functions as an alternative when your 'lookup value' is not in the first row.
* You can use the OFFSET and MATCH functions as an alternative when your 'lookup value' is not in the first row.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 ABCD
1=HLOOKUP("Wheels",B1:D2,2,TRUE) = 11WheelsBearingsBolts
2=HLOOKUP("Wheels",B1:D2,1,TRUE) = Bolts6811
3=HLOOKUP("Wheels",B1:D2,1) = Bolts   
4=HLOOKUP("Wheels",B1:D3,2) = 11   
5=HLOOKUP("Wheels",B1:D3,1,FALSE) = Wheels   
6=HLOOKUP("Wheels",B2:D4,2,FALSE) = #N/A   


© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited Top