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_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.
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


* Only available in Microsoft 365 and was released in February 2020.
* 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 two-dimensional 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.
* 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=XLOOKUP(1,{1,2,3},{4,5,6}) = 4
2=XLOOKUP(2,{1,2,3},{4,5,6}) = 5
3=XLOOKUP(4,{1,2,3},{4,5,6}) = #N/A
4=XLOOKUP(4,{1,2,3},{4,5,6},"four") = four

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