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_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.
not_found	(Optional) The value to return when no match is found.
match_mode	(Optional) A number indicating the type of match: -1 = Closest match, exact or closest value less than it 0 (False) = Exact match, the lookup_array does not have to be sorted (default) 1 (True) = Closest match, exact or 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' = -1, then 'lookup_array' must be sorted into ascending order (-1 ,0, 1, A, a, FALSE, TRUE).
* If 'match_mode' = 0, then 'lookup_array' does not have to be sorted.
* If 'match_mode' = 1, then 'lookup_array' must be sorted into ascending order (-1, 0, 1, A, a, FALSE, TRUE).
* 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

	A	B	C	D
1	=XLOOKUP("Tokyo", B1:B3, C1:C3) = 6	Paris	8
2	=XLOOKUP("Paris", B1:B3, C1:C3) = 8	Rome	11
3	=XLOOKUP("Rome", B1:B3, C1:C3, , 0) = 11	Tokyo	6
4	=XLOOKUP("NoMatch", B1:B3, C1:C3, "NoMatch", 0) = NoMatch
5	=XLOOKUP("Rom", B1:B3, B1:B3, , -1) = Paris		Tokyo	6
6	=XLOOKUP("Romx", B1:B3, B1:B3, , -1) = Rome		Rome	11
7	=XLOOKUP("Tok", B1:B3, B1:B3, , -1) = Rome		Paris	8
8	=XLOOKUP("Par", B1:B3, B1:B3, , -1) = #N/A
9	=XLOOKUP("Rom", B1:B3, B1:B3, , 1) = Rome	Paris	Rome	Tokyo
10	=XLOOKUP("Romx", B1:B3, B1:B3, , 1) = Tokyo	6	11	8
11	=XLOOKUP("Tok", B1:B3, B1:B3, , 1) = Tokyo
12	=XLOOKUP("Par", B1:B3, B1:B3, , 1) = Paris
13	=XLOOKUP("Toky", C5:C7, C5:C7, , -1) = Rome
14	=XLOOKUP("Rom", C5:C7, C5:C7, , -1) = Paris
15	=XLOOKUP("Wrong", C5:C7, C5:C7, , -1) = Tokyo
16	=XLOOKUP("Ab", B9:D9, B10:D10) = #N/A
17	=XLOOKUP(TRUE, EXACT(B1:B3, "AA"), C1:C3) = #N/A

1 - Return the value in the range "C1:C3" from the row that matches "Tokyo" in the range "B1:B3". Exact match is the default.
2 - Return the value in the range "C1:C3" from the row that matches "Paris" in the range "B1:B3". Exact match is the default.
3 - Return the value in the range "C1:C3" from the row that matches "Rome" in the range "B1:B3". Exact match is specified explicitly.
4 - Return the value in the range "C1:C3" from the row that matches "NoMatch" in the range "B1:B3". There is no match, so the "not_found" argument is returned.
5 - Return the value in the range "B1:B3" from the row that has the closest value less than "Rom" in the range "B1:B3". The value above "Rom" is "Paris".
6 - Return the value in the range "B1:B3" from the row that has the closest value less than "Tok" in the range "B1:B3". The value above "Romx" is "Paris".
7 - Return the value in the range "B1:B3" from the row that has the closest value less than "Tok" in the range "B1:B3". The value above "Tok" is "Rome".
8 - Return the value in the range "B1:B3" from the row that has the closest value less than "Par" in the range "B1:B3". The value above "Par" is smaller than the smallest value.
9 - Return the value in the range "B1:B3" from the row that has the closest value greater than "Rom" in the range "B1:B3". The value
10 - Return the value in the range "B1:B3" from the row that has the closest value greater than "Tok" in the range "B1:B3". The value
11 - Return the value in the range "B1:B3" from the row that has the closest value greater than "Tok" in the range "B1:B3". The value
12 - Return the value in the range "B1:B3" from the row that has the closest value greater than "Par" in the range "B1:B3". The value
13 - Return the value in the range "C5:C7" from the row that has the closest value less than "Toky" in the range "C5:C7".
Incorrect. The column is not sorted. The value "Toky" is smaller than the first value, so returning "Rome" is very confusing.
14 - Return the value in the range "C5:C7" from the row that has the closest value less than "Rom" in the range "C5:C7".
Incorrect. The column is not sorted. The value above "Rom" is "Tokyo", so returning "Paris" is very confusing.
15 - Return the value in the range "C5:C7" from the row that has the closest value less than "Wrong" in the range "C5:C7".
Incorrect. The column is not sorted. The value "Wrong" is larger than the last value, so returning "Tokyo" seems to be correct which is very confusing.
16 - Find the column containing "Ab" in row 9 and return the value from row 10.
17 - Find the row containing "AA" in column "B" with a case sensitive match. Instead of True you could also use 1.