How to Find the LAST Matching Value (Not the First) in a List

Quick gut-check: if the same Order ID, ticket number or SKU shows up more than once in your data — because you log every status change instead of overwriting it — what does VLOOKUP hand you back?

Not the current status. The oldest one. Every single time.

It’s one of the sneakiest lookup mistakes in Excel, and almost nobody notices it’s happening, because the formula never throws an error. It just confidently reports yesterday’s news as today’s truth.

Grab a coffee. We’ll fix it properly: the trick that’s worked since Excel 2007, then the one-line 365 upgrade that replaces it and says out loud what it’s doing.

Who this quietly bites

Any time you keep an append-only log — you add a new row every time something changes instead of editing the old one — you’ll hit this. It’s the right way to keep data, because you never lose history. It’s also the exact shape a plain lookup cannot read.

• Order and shipment tracking — Order Placed → Processing → Shipped → Delivered, one row per status change.
• CRM deal stages — every stage change logged, same Deal ID repeated.
• Price and inventory history — a price list where each SKU gets a new row every time it changes.
• Attendance and check-in logs — same employee ID, one row per clock-in.
• Support ticket updates — same ticket number, one row per reply or status change.

If any of that sounds like a sheet you maintain, keep reading.

Why VLOOKUP (and INDEX-MATCH) get it wrong

VLOOKUP, HLOOKUP and a default INDEX(MATCH()) all do the same thing under the hood: they scan top to bottom and stop at the first match. That’s exactly right when a key appears once. It’s exactly wrong when a key repeats and you want the most recent row.

Here’s a slice of a real order log — status updates logged chronologically as they happen, not grouped by order:

Ask =VLOOKUP("ORD-1001", A:C, 3, FALSE) for the current status and it returns “Order Placed.” ORD-1001 was delivered five days ago.

And it gets worse than “out of date.” One of the orders in the workbook is delivered on 12 August and returned on the 18th. Another is cancelled and never ships at all. For those, only the most recent row tells the truth — and the first row tells a story that is not merely stale but actively wrong.

The fix, two ways

The scenario, in full. Thirty log rows across eight orders on an Order Log sheet, rows 4 to 33: Order ID in column A, date in B, status in C, order value in D. Sorted by date, because that’s how a log arrives.

What you need is a lookup that searches from the bottom up and stops at the first match it finds — which, read top-to-bottom, is the last occurrence.

Option 1 — Classic (backward-compatible functions, step by step)

This is a genuinely famous power-user trick, and it’s worth learning even if you’re on 365, because it travels safely into any file a client or colleague might open on an old version.

Step 1 — Keep the log append-only.

Three columns is enough: key, date, status. New events get appended as new rows. Never edit history in place — that’s the habit that makes everything else here possible.

Step 2 — List each unique key once on a summary sheet.

One row per Order ID, Deal ID, SKU, whatever your key is.

Step 3 — Pull the latest status and date beside each key.

Status:  =LOOKUP(2, 1/('Order Log'!$A$4:$A$33=$A5), 'Order Log'!$C$4:$C$33)
Date:    =LOOKUP(2, 1/('Order Log'!$A$4:$A$33=$A5), 'Order Log'!$B$4:$B$33)

Copy both down. Every cell independently re-scans the whole log and returns whatever the current last match is — add ten more rows tomorrow and these keep working with no maintenance.

Option 2 — Excel 365 spill (dynamic-array equivalent)

On 365, skip the division trick entirely. XLOOKUP has a search-direction argument built right in:

=XLOOKUP(criteria, LookupRange, ReturnRange, "Not found", 0, -1)

Those last two arguments are the whole trick: 0 means exact match (the default, but worth being explicit), and -1 means search last-to-first.

Status:  =XLOOKUP($A5, 'Order Log'!$A$4:$A$33, 'Order Log'!$C$4:$C$33, "Not found", 0, -1)
Date:    =XLOOKUP($A5, 'Order Log'!$A$4:$A$33, 'Order Log'!$B$4:$B$33, "Not found", 0, -1)

Same result as the LOOKUP trick, every time — but readable at a glance by anyone who’s ever used VLOOKUP, and with no need to explain why dividing by FALSE works to whoever inherits the sheet.

Flip that -1 to 1 and you get the first occurrence instead. First seen and last seen, same function, one argument apart — which is how the workbook computes each order’s days-in-pipeline.

Bonus: the whole history, not just the latest row

This is where dynamic arrays genuinely pull ahead. Instead of one value, spill an item’s entire history, most recent first:

=SORT(FILTER('Order Log'!$A$4:$D$33, 'Order Log'!$A$4:$A$33=$B$3, "No events logged"), 2, -1)

FILTER pulls every row for that key; SORT(..., 2, -1) reorders the result by column 2 — the date — descending. The third argument to FILTER is the empty case: without it, a key with no rows returns #CALC! and the sheet looks broken rather than empty.

Pair the criteria cell with a data-validation dropdown of your unique keys and you have a self-service “show me everything that happened to this order” report that updates the instant you pick a different key. No pivot table, no manual re-filtering.

For the dropdown source itself, =UNIQUE('Order Log'!$A$4:$A$33) spills every key in the log and grows the moment a new one is logged — nothing to maintain.

Where else this pattern pays for itself

• Order and shipment tracking — always show the current stage, not the oldest one.
• CRM pipelines — pull each deal’s most recent stage from a stage-change log instead of overwriting a single Stage cell and losing the history.
• Price and cost history sheets — get the current price per SKU from a log of every change, while keeping the full history for audit.
• Support and ticketing logs — surface the latest update per ticket for a status dashboard.
• Attendance and check-in sheets — find each person’s most recent check-in.
• Slowly changing dimensions, if you’ve met the term — this is the spreadsheet version of a Type 2 dimension, and “give me the current row” is exactly the query a data warehouse answers with a validity flag.

The shape is always the same: a key that repeats because you’re correctly logging history, and a need to answer “what’s true right now” without losing any of it.

Download the workbook

The order-log example above — 30 rows, 8 orders, statuses logged exactly as they’d happen in real life, including one order that’s cancelled and one that’s delivered and then returned.

Five sheets: Order Log is the raw log; yellow cells are yours, so add a row at the bottom and every formula in the file picks it up. Current Status puts four answers side by side per order — what VLOOKUP returns, what the LOOKUP trick returns, what XLOOKUP with search mode -1 returns, and a match-check column proving the last two always agree, plus a count of how many orders VLOOKUP gets wrong. Full History (365) has the dropdown driving the spilled SORT(FILTER(...)), with first-seen, last-seen and days-in-pipeline underneath. Check ties the two correct methods together across all eight orders and re-derives every latest date with the SUMPRODUCT(MAX(...)) route above, which never looks at row order.

Download the last-match lookup workbook (.xlsx)

The XLOOKUP, FILTER and SORT columns need Microsoft 365 or Excel 2021+. The LOOKUP column opens in anything back to 2007, which is usually what the machine you’re emailing this to is running.

Takeaways

• VLOOKUP, HLOOKUP and a default INDEX(MATCH()) all return the first match — dead wrong the moment a key repeats because you’re logging history instead of overwriting it.
• It’s not an edge case. “Order Placed” is logged first for every order, so a first-match lookup reports every order in the sheet as still at step one. In the workbook that’s eight out of eight.
• =LOOKUP(2, 1/(range=criteria), return_range) returns the last match instead, works in every Excel since 2007, and needs no array entry. The #DIV/0! errors are the mechanism, not a side effect.
• On 365, =XLOOKUP(criteria, range, return_range, "Not found", 0, -1) does the same thing in one readable function. Flip the -1 to 1 for the first occurrence.
• Wrap FILTER in SORT to spill an item’s entire history, most recent first — and give FILTER its third argument so the empty case reads “no events” rather than #CALC!.
• Point these at a Table, not at whole columns. 1/(A:A=criteria) evaluates a million rows per cell.
• Your move: open any log-style sheet you maintain and check one summary formula. If it’s a VLOOKUP and the key repeats, you already know what it’s been telling you.