Lookup FunctionsAdvanced

Find the Last Price Paid with a Reverse XLOOKUP

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.

Task:

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.

Interactive Spreadsheet

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

The data in this exercise

This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.

10 rows × 7 columns4 cells you fill in
ABCDEFG
1Order DatePartUnit Price ($)PartLast Price Paid ($)
21/8/2026Bearing 62054.85Bearing 6205
32/3/2026V-belt A4211.2V-belt A42
43/17/2026Bearing 62055.1Hydraulic filter HF-30
54/2/2026Hydraulic filter HF-3023.4Coupling insert L-100
65/21/2026V-belt A4210.75
76/9/2026Bearing 62055.35
87/14/2026Hydraulic filter HF-3024.9
98/26/2026V-belt A4212.05
109/30/2026Bearing 62055.6
What this exercise teachesMay contain the answer

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.