After the first three arguments, XLOOKUP has three optional ones:
- if_not_found: what to show instead of #N/A, such as
"None" or 0.
- match_mode:
0 exact (the default), -1 exact or the next smaller item, 1 exact or the next larger item, 2 wildcards.
- search_mode:
1 from the first row down (the default), -1 from the last row up.
Searching from the end answers "what was the latest price?" in a log where the newest rows are at the bottom. Next larger answers "which box size fits?" without sorting anything. To skip an optional argument, leave it empty between commas: =XLOOKUP(A2,D:D,E:E,,1).