Explanation
XLOOKUP offers several features that make it exceptionally good for more complicated lookups.
In this example, we want the latest price for an itemby date.
If data were sorted by date in ascending order, this would bevery straightforward.
However, in this case,data is unsorted.
By default, XLOOKUP will return thefirst matchin a data set.
This array acts like a filter.
XLOOKUP looks through the lookup array for the maximum date value.
The final result is the price associated with the latest date.
The formula will continue to work when the data sorted in any order.
XLOOKUP supports approximate and exact matching, wildcards (* ?)
MAX ignores empty cells, the logical values TRUE and FALSE, and text values.
