Solved: How to use LET and XLOOKUP to return the most recent price from a non-sorted table with multiple criteria in Excel

📌 The Problem Explained

Imagine you manage a procurement log where price updates are entered manually. Over time, the table becomes a mess: prices for the same “Product” across different “Regions” are added out of order. Some entries from 2023 are at the bottom, while new entries from 2024 are buried in the middle.

A standard VLOOKUP or a basic XLOOKUP usually returns the first match it finds. If your data isn’t sorted by date, these functions will return an outdated price. To find the most recent price based on multiple criteria (e.g., Product = “Laptop” AND Region = “North”) without manually sorting your sheet every time, you need a dynamic formula.

💡 The Quick Solution

Use this formula structure. It uses LET to organize the logic, SORT to handle the date order, and XLOOKUP to find the match.

=LET(
    Data, A2:D100,
    SortedData, SORT(Data, 3, -1),
    ProdCol, INDEX(SortedData,, 1),
    RegCol, INDEX(SortedData,, 2),
    PriceCol, INDEX(SortedData,, 4),
    XLOOKUP(1, (ProdCol="Widget A") * (RegCol="North"), PriceCol, "Not Found")
)

🛠️ Step-by-Step Breakdown

To implement this, ensure your data is in a range or a table. In the example below, we assume: Column A is Product, Column B is Region, Column C is Date, and Column D is Price.

Step	Action	Logic
1	Define Data	We use LET to name our source range Data. This makes the formula easier to read and faster to calculate.
2	Sort by Date	SORT(Data, 3, -1) takes our table and sorts it by the 3rd column (Date) in descending order (-1). This puts the newest dates at the top.
3	Assign Columns	We use INDEX to “pick out” specific columns from our newly sorted data so we can check criteria against them.
4	Apply Criteria	In XLOOKUP, we use (ProdCol="Widget A") * (RegCol="North"). This creates an array of 1s and 0s where ‘1’ signifies both conditions are met.
5	Return Value	XLOOKUP looks for the first 1 it finds. Since the data is sorted newest-to-oldest, the first 1 found is guaranteed to be the most recent price.

How to apply it to your sheet:

1. Select the cell where you want the result.
2. Go to the Formula Bar.
3. Paste the formula and update the range (e.g., A2:D100) to match your actual data.
4. Update the column indices in the INDEX functions to match your column order.

⚠️ Common Mistakes to Avoid

• Wrong Sort Order: Ensure the SORT function uses -1 for descending order. If you use 1 (ascending), the formula will return the oldest price instead of the newest.
• Boolean Logic Syntax: When using multiple criteria in XLOOKUP, you must search for the value 1 and use the multiplication * symbol between your criteria brackets. This acts as “AND” logic.
• Data Types: Ensure your Date column is actually formatted as a Date (number) and not as Text. If Excel sees the dates as text, the SORT function will sort them alphabetically (e.g., “August” before “January”), leading to incorrect results. Check this via Home > Number Format.