📎 This article includes 1 downloadable practice file ↓
Your price list has the same product many times — the newest row is at the bottom. VLOOKUP returns the first (oldest) price. You want the last. There is a formula that has been passed around Excel forums for years because it works in every version:
In this article
=LOOKUP(2, 1 / (A2:A100 = G2), C2:C100)Why on earth does that work?
(A2:A100 = G2)gives TRUE/FALSE for each row.1 / TRUE= 1 and1 / FALSE=#DIV/0!. So we get an array like{#DIV/0!; 1; #DIV/0!; 1; #DIV/0!}.- LOOKUP searches for 2, which can never be found (the biggest value is 1).
- When LOOKUP cannot find the value it returns the last number it saw — and it silently ignores errors. The last 1 is the last matching row.
- LOOKUP then returns the value from C in that same position.
💡 It works with several conditions too:
=LOOKUP(2, 1/((A2:A100=G2)*(B2:B100=H2)), C2:C100).The modern way: XLOOKUP search mode
=XLOOKUP(G2, A2:A100, C2:C100, "Not found", 0, -1)The last argument -1 tells XLOOKUP to search from the bottom up. Clearer, faster, and it has a “not found” value — use it if everyone has Microsoft 365 or Excel 2021.
Real uses
| Question | Formula idea |
|---|---|
| Latest price of an item | Last match of the item code → price column |
| Current status of a ticket | Last match of ticket ID → status |
| Date of the last payment from a customer | Last match of customer → date |
| Last non-empty cell in a column | =LOOKUP(2, 1/(C2:C1000<>""), C2:C1000) |
⚠️ The trick assumes the list is in chronological order (newest at the bottom). If rows can be in any order, use
MAXIFS on the date instead: =MAXIFS(D2:D100, A2:A100, G2) gives the latest date, then look up with both keys.Try it yourself: step by step
- Download last-match-practice.xlsx. Price history holds 40 dated price changes, oldest first.
- Open Latest. Columns B and C (LOOKUP trick and XLOOKUP -1) show the same, most recent price for each product.
- Column E uses a normal VLOOKUP — it returns the oldest price. This is the classic mistake the article solves.
- Column D uses
MAXIFSto show the date of the last change. - Add a new row at the bottom of Price history for “Mouse” with today’s date and a new price, then extend the ranges — the Latest sheet updates.
📎 Practice files for this article
- 📗Last-match workbook40 price changes + LOOKUP(2,1/u2026), XLOOKUP -1, MAXIFS and VLOOKUP compared.⬇ XLSX · 7 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.