A purchase log lists the same part many times, oldest first, and a plain lookup stops at the first match: the oldest price. XLOOKUP's sixth argument turns the search around so it starts from the bottom, and the first match it finds is the most recent one.
You are the maintenance buyer at a packaging plant, and you are refreshing the spares budget. The purchase log in A2:C10 records every order of the plant's regular spares, oldest first, and the same part appears several times at different prices. In G2, use XLOOKUP to return the most recent unit price paid for the part in F2, then copy it down to G5. The coupling insert in F5 has never been ordered, so it should show "No history" rather than an error.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Order Date | Part | Unit Price ($) | Part | Last Price Paid ($) | ||
| 2 | 1/8/2026 | Bearing 6205 | 4.85 | Bearing 6205 | |||
| 3 | 2/3/2026 | V-belt A42 | 11.2 | V-belt A42 | |||
| 4 | 3/17/2026 | Bearing 6205 | 5.1 | Hydraulic filter HF-30 | |||
| 5 | 4/2/2026 | Hydraulic filter HF-30 | 23.4 | Coupling insert L-100 | |||
| 6 | 5/21/2026 | V-belt A42 | 10.75 | ||||
| 7 | 6/9/2026 | Bearing 6205 | 5.35 | ||||
| 8 | 7/14/2026 | Hydraulic filter HF-30 | 24.9 | ||||
| 9 | 8/26/2026 | V-belt A42 | 12.05 | ||||
| 10 | 9/30/2026 | Bearing 6205 | 5.6 |
A lookup returns the first match it meets, so the question is never just which value matches but which one it meets first. Read top-down, the log meets the bearing in January at $4.85 and stops there, three price rises out of date; the formula looks finished and would quietly under-budget every part on the list. A search mode of -1 tells XLOOKUP to begin at row 10 and climb, and because the log is in date order, the first match from the bottom is the latest order: $5.60 for the bearing, bought in September. The V-belt shows this is about position, not size: its prices went $11.20, $10.75, $12.05, so neither the highest nor the lowest price is the right answer, only the last one is, and MAX or MIN would each be right for some parts and wrong for others. The fourth argument, "No history", replaces the #N/A the coupling insert would otherwise show, and it has to be there anyway, because search mode is the sixth argument and the fourth and fifth cannot be skipped to reach it. The $ signs keep B2:B10 and C2:C10 fixed as the formula moves down while F2 moves with it, so one formula serves the whole list. The method only holds while the log stays in date order; a row pasted in out of sequence becomes "the last order" whatever its date.