XLOOKUP

XLOOKUP(lookup_value, lookup_array, return_array [,not_found] [,match_mode] [,search_mode])

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

lookup_valueThe value to find in the lookup_array.
lookup_arrayThe array of values that contain the value you want to look for.
return_arrayThe range of cells that contains the value (or values) you want to return.
not_found(Optional) The value to return when no match is found.
match_mode(Optional) A number indicating the type of match:
0 (False) = Exact match, lookup_array does not have to be sorted (default)
-1 = Closest match, exact or the closest value less than it
1 (True) = Closest match, exact or the closest value greater than it
2 = Wildcard character match (? and *)
search_mode(Optional) A number indicating the search mode:
1 = Linear search, from first to last (default)
-1 = Linear search, from last to first
2 = Binary search, lookup_array must be sorted into ascending order
-2 = Binary search, lookup_array must be sorted into descending order

REMARKS
* For an illustrated example refer to the page under Lookup Functions
* This function was added in Excel 2021 to replace the VLOOKUP, HLOOKUP and LOOKUP functions.
* This function is not case sensitive when matching 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 (~~).
* If "lookup_value" cannot be found and "match_mode" = 0, then #N/A is returned.
* If "lookup_array" is a two-dimensional range and not a single column (or row) reference, then #VALUE! is returned.
* If "lookup_array" contains a different number of items to "return_array", then #VALUE! is returned.
* If "not_found" is left blank and the function cannot find a match, then #N/A is returned.
* If "match_mode" is left blank, then 0 is used.
* If "match_mode" is not 0,1,-1,2,True or False, then #VALUE! is returned.
* If "search_mode" is left blank, then 1 is used.
* You can use the XMATCH function to return the position of a value.
* 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 OFFSET function to return a value in an offset position.
* You can use the CHOOSE function to return the value in a list based on an index number.
* You can use the EXACT function to perform a case sensitive match.
* You can use the IFNA function to return the value or something else if it evaluates to #N/A.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

 ABCD
1=XLOOKUP("Cc", B1:B3, C1:C3) = 6Aa8 
2=XLOOKUP("Aa", B1:B3, C1:C3) = 8Ab11 
3=XLOOKUP("AA", B1:B3, C1:C3) = 8Cc6 
4=XLOOKUP(TRUE, EXACT(B1:B3, "AA"), C1:C3) = #N/A   
5=XLOOKUP("Cc", B1:B3, B1:B3) = CcCcAbAa
6=XLOOKUP("C", B1:B3, C1:C3, "NotFound") = NotFound6118
7=XLOOKUP("C", B1:B3, B1:B3, "NextSmallest", -1) = Ab   
8=XLOOKUP("C", B1:B3, B1:B3, "NextLargest", 1) = Cc   
9=XLOOKUP("C", B1:B3, B1:B3, "PassingTrue", TRUE) = Cc   
10=XLOOKUP("Ab", B5:D5, B6:D6) = 11   

1 - Find the row containing "Cc" in column "B" and return the value from column "C".
2 - Find the row containing "Aa" in column "B" and return the value from column "C".
3 - Find the row containing "AA" in column "B" and return the value from column "C". This function is not case sensitive.
4 - Find the row containing "AA" in column "B" with a case sensitive match. Instead of True you could also use 1.
5 - Find the row containing "Cc" in column "B" and return the value from column "B".
6 - Find the row containing "C" in column "B" and if there is no match, return the string "NotFound".
7 - Find the closest match (or next smallest) to the value "C" in column "B".
8 - Find the closest match (or next largest) to the value "C" in column "B".
9 - What happens when you pass True as the match mode. The closest match (or next smallest) is returned.
10 - Find the column containing "Ab" in row 5 and return the value from row 6.

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