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, most common 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.

Let’s fix that properly: the trick that’s worked since Excel 2007, and the one-line Excel 365 upgrade that replaces it.

Who this quietly breaks

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 (you never lose history), but it means the same key appears multiple times, and a plain lookup has no idea which occurrence you actually want.

• Order/ticket tracking — Order Placed → Processing → Shipped → Delivered, one row per status change.
• CRM deal stages — every stage change logged, same Deal ID repeated.
• Price or inventory history — a price list where each SKU has a new row every time it changes.
• Attendance or 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 — this is the formula you’ve been missing.

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 what you want when a key appears once. It’s exactly wrong when a key appears repeatedly 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. Run this across a full order log and the pattern is brutal: because “Order Placed” is always the first status logged for every order, a naive lookup reports that every single order in the sheet is still sitting at step one — regardless of what’s actually happened since. It’s not subtly wrong. It’s confidently, uniformly wrong, and it’ll pass a quick glance-test because the formula returns a real status, not an error.

The fix, two ways

What you actually 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. Here are two ways to get there.

Option 1 — the LOOKUP trick: works in every Excel since 2007

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:

=LOOKUP(2,1/(A:A=criteria),ReturnRange)

Why this works (it looks like witchcraft the first time you see it): 1/(A:A=criteria) turns every matching row into 1 and every non-matching row into #DIV/0! — dividing 1 by FALSE, which Excel treats as 0. LOOKUP is then asked to find 2 in that array of 1s and errors. Since 2 never actually appears, LOOKUP falls back to its documented behavior: it silently ignores the error cells and returns the value from ReturnRange next to the last non-error match it saw. That “last non-error match” is exactly the last row where your condition was true — no sorting required, no array-entry (CTRL+SHIFT+ENTER) needed, just enter it like a normal formula.

Build it yourself in 3 steps:

1. Keep your log as a plain, ever-growing table. Three columns is enough: key, date, status (or whatever value you’re tracking). New events just get appended as new rows — never edit history in place.

2. On a summary sheet, list each unique key once. One row per Order ID, Deal ID, SKU, whatever your key is.

3. Pull the latest status and latest date next to each key:

Status:  =LOOKUP(2,1/('Order Log'!A:A=A2),'Order Log'!C:C)
Date:    =LOOKUP(2,1/('Order Log'!A:A=A2),'Order Log'!B:B)

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

Option 2 — Excel 365: XLOOKUP’s search_mode argument does it in one shot

If you’re on 365, skip the division trick entirely. XLOOKUP has a search-direction argument built right in — set it to -1 and it searches from the bottom of the range upward, stopping (and returning) at the very first match it finds, which is the last one in your log:

=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. No error-division sleight of hand, no needing to explain “why does dividing by FALSE work” to whoever inherits the sheet — just a function that says exactly what it does.

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

Same result as the LOOKUP trick, every time — but readable at a glance by anyone who’s ever used VLOOKUP.

Bonus: want the whole history, not just the latest row?

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

=SORT(FILTER('Order Log'!B:C, 'Order Log'!A:A=criteria), 1, -1)

FILTER pulls every row for that key; SORT(..., 1, -1) reorders the result by column 1 (the date) descending. Pair the criteria cell with a data-validation dropdown of your unique keys, and you’ve got a self-service “show me everything that happened to this order” mini-report that updates the instant you pick a different key — no pivot table, no manual re-filtering.

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 price change, while still keeping the full history for audits.
• Support and ticketing logs — surface the latest update per ticket for a status dashboard.
• Attendance/check-in sheets — find each person’s most recent check-in or most recent status.

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 that history.

Download the workbook

The order-log example above — 30 rows, 8 orders, statuses logged exactly as they’d happen in real life — lives in a free workbook, along with a live “pick an order, see its whole history” demo. Amber cells are inputs; the Current Status tab runs both the backward-compatible LOOKUP trick and the 365 XLOOKUP side by side with a Match Check column so you can watch them agree; the Full History (365) tab has a dropdown that drives a spilled SORT(FILTER(...)) formula.

Download the last-match lookup workbook (.xlsx)

Takeaways

• VLOOKUP, HLOOKUP and a default INDEX(MATCH()) all return the first match in a range — dead wrong the moment a key repeats because you’re logging history instead of overwriting it.
• =LOOKUP(2,1/(range=criteria),return_range) returns the last match instead, works in every Excel version since 2007, and needs no array-entry.
• On Excel 365, =XLOOKUP(criteria, range, return_range, "Not found", 0, -1) does the exact same thing in one readable function — the -1 search mode searches bottom-to-top.
• Wrap FILTER in SORT (365 only) to spill an item’s entire history, most recent first, instead of just the latest value.
• This bug hides well: it never throws an error, it just quietly reports old data as current — check any log-style sheet in your workbook for it.