XLOOKUP(search, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])
search - The value to find. Text matches whatever its case, and a number matches the same number written as text.
lookup_range - The cells to search, such as A1:A6 or a column like [Item].
return_range - The cells to return from, as long as lookup_range. The value in the matching position comes back.
if_not_found optional - What to return when nothing matches. Leave it out to get #N/A, or write NA() to give a match_mode without a fallback.
match_mode optional - Use 0 (the default) for an exact match, -1 for an exact match or else the next smaller value, 1 for an exact match or else the next larger value, or 2 to read * and ? in search as wildcards.
search_mode optional - Use 1 (the default) to search from the top, or -1 from the bottom. 2 and -2 are Excel's binary search over data sorted smallest or largest first; Blackbear searches from the top, which finds the same match in sorted data.
Example: =XLOOKUP("Lunch", A1:A6, C1:C6) gives 14.75.
It returns the first match in search order. With match_mode -1 or 1, numbers compare with numbers and text with text, alphabetically and ignoring case, so =XLOOKUP(42, C1:C6, A1:A6, NA(), -1) finds the item with the largest amount up to 42.