XLOOKUP(lookup_value, lookup_array, return_array [,if_not_found] [,match_mode] [,search_mode]) 
Returns the value in the same row after finding a matching value in any column. 
lookup_value  The value to find in the lookup_array. 
lookup_array  The array of values that contain the value you want to look for. 
return_array  The range of cells that contains the value (or values) you want to return. 
if_not_found  (Optional) The value to return when no match is found. 
match_mode  (Optional) The match mode to use: 0 = Exact match is returned (lookup_array does not have to be sorted) (default) 1 = Exact match or the next smallest item 1 = Exact match or the next largest item 2 = Wildcard character match (? and *) 
search_mode  (Optional) The search mode to use: 1 = First to Last (default) 1 = Last to First 2 = First to Last (case sensitive) lookup_array must be sorted in ascending order 2 = Last to First (case sensitive) lookup_array must be sorted into descending order 
Remarks 
* Added in Excel 2021. * For an illustrated example refer to the page under Lookup Functions * This function replaces the VLOOKUP, HLOOKUP and LOOKUP functions. * This function is not case sensitive by default but this can be changed using the "search_mode" argument. * This function supports wildcards (? and *). * Wildcard character: ? = a single character. * Wildcard character: * = multiple characters. * If "lookup_value" cannot be found and "match_mode" = 0, then #N/A is returned. * If "lookup_value" cannot be found and "match_mode" = 1 it uses the largest value that is less than the "lookup_value". * If "lookup_value" cannot be found and "match_mode" = 1 it uses the smallest value that is greater than the "lookup_value". * If "lookup_array" is a twodimensional range and not a single column(or row) reference, then * If "lookup_array" contains a different number of items to "return_array", then #VALUE! is returned. * If "if_not_found" is left blank, the function cannot return no matches. * If "if_not_found" is left blank and the function returns no matches, then #N/A is returned. * If "match_mode" is left blank, then 0 is used. * If "search_mode" is left blank, then 1 is used. * This function was released in March 2020. * You can use the INDEX function to return the intersection of a row and a column. * You can use the XMATCH function to return the position of a value in a list, table or cell range. * For the Microsoft documentation refer to support.microsoft.com 

1  Return the value from the array {4,5,6} which corresponds to the position of the value "1" in the array {1,2,3}. 2  Return the value from the array {4,5,6} which corresponds to the position of the value "2" in the array {1,2,3}. 3  The value "4" does not exist in the array {1,2,3}, #N/A is returned. 4  The value "4" does not exist in the array {1,2,3}, because there is a "if_not_found" arguments passed in, this is returned instead of #N/A. 
© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited Top