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 to be found 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) Whether to find an exact match: 1 / True = Exact match or the next smallest item (table_array must be sorted) (default) 0 / False = Exact match is returned (table_array does not have to be sorted) 
Remarks 
* For an illustrated example refer to the page under Lookup Functions. * XLOOKUP was added in Microsoft 365 to replace this function. * 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 should be used when you want to lookup a matching value in a particular row of a table and then return a value in the corresponding column. * 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 a one of these. * If "lookup_value" is text, then it can also include the two wildcard characters (*) and (?). * If "lookup_value" is smaller than the smallest value in the first row of "table_array", then #N/A is returned. * If "lookup_value" cannot be found and "range_lookup" is True, it uses the largest value that is less than the "lookup_value". * 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" is left blank, then True is used. * If "range_lookup" = True, then an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than "lookup_value" is returned. * If "range_lookup" = True, then "table_array" must be sorted into ascending order (1, 0, 1, a, A, FALSE, TRUE). * If "range_lookup" = False, then "table_array" does not have to be sorted. * If "range_lookup" = False, then an exact match will be found. If one is not found, then #N/A is returned. * 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.office.com 

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