Lookup & Reference
Lookup latest price or revision by date (unsorted history safe)
Retrieve the most recent unit cost or contract rate for an item even when historical log rows are NOT sorted chronologically.
Why reverse XLOOKUP(..., -1) fails on unsorted imports—and MAXIFS fixes it
Bottom-to-top XLOOKUP(SKU, SKUCol, PriceCol, , 0, -1) assumes the newest date always sits at the bottom of the sheet. If backdated invoices or CSV merges arrive out of order, bottom-to-top search grabs an outdated price!
Combining MAXIFS(DateCol, SKUCol, TargetSKU) with boolean XLOOKUP(1, (SKUCol=TargetSKU) * (DateCol=LatestDate), PriceCol) guarantees the chronologically newest effective date wins regardless of row order.
| SKU (A) | EffectiveDate (B) | UnitCost (C) | Chronological Note |
|---|---|---|---|
| SKU-A | 2026-01-15 | 42 | Oldest |
| SKU-A | 2026-06-01 | 49 | LATEST date (middle row!) |
| SKU-B | 2026-05-10 | 85 | Different SKU |
| SKU-A | 2026-03-20 | 45 | Backdated row at bottom! |
Order-independent latest-by-date lookup formula
Bind maxDt = MAXIFS(...) inside LET so the latest date is computed once and matched alongside the SKU.
=LET(maxDt, MAXIFS(B2:B5, A2:A5, "SKU-A"), IF(maxDt=0, "Not Found", XLOOKUP(1, (A2:A5="SKU-A") * (B2:B5=maxDt), C2:C5)))Contrast naive bottom-row lookup on unsorted rows
Reverse search (-1) mistakenly grabs row 5 ($45 from March) instead of row 3 ($49 from June)!
=XLOOKUP("SKU-A", A2:A5, C2:C5, "Not Found", 0, -1)Inputs and interpretation
Require positive numeric date serials and literal SKU identifiers without wildcard characters for this MAXIFS example. Ties at the latest date use the first matching row, so conflicting same-date revisions need an additional version key. The maximum includes future dates; a current-as-of price requires a date cutoff.
Try the example
Use the original values or change them to test your understanding.