Get the Most Recent (Last) Match

Excel 365Legacy altBottom-up

A normal lookup returns the first match from the top. When rows are in date order and you want the latest entry — the most recent price, the last status — you need to search from the bottom up.

Quick formula: to get the last value matching E2, searching bottom-to-top:

=XLOOKUP(E2, A2:A8, C2:C8, , 0, -1)

The final argument -1 is search_mode: search last-to-first, so the newest matching row wins.

Functions used (tap for the full reference guide):

XLOOKUP LOOKUP

The example

An order log in date order. We want Acme’s most recent amount.

	A	B	C
1	Customer	Date	Amount
2	Acme	1/05	$120
3	Bolt	2/03	$80
4	Acme	3/18	$200
5	Acme	5/30	$260
6	Bolt	6/01	$90

The formula

Acme’s latest amount:

=XLOOKUP("Acme", A2:A6, C2:C6, , 0, -1) // last Acme row → $260

How it works

The search direction is everything:

The first three arguments are an ordinary exact-match lookup of "Acme".
The 5th argument, match_mode 0, keeps it an exact match.
The 6th argument, search_mode -1, tells XLOOKUP to scan from the bottom up, so the first hit it finds is the last one in the list.
With the log in date order, that last hit is the most recent — $260.

Try it: interactive demo

Live demo

Pick a customer and a direction; see which amount XLOOKUP returns.

Customer

Search mode

Returns:

Variations

Legacy LOOKUP trick (any version)

The classic way to get the last match before XLOOKUP:

=LOOKUP(2, 1/(A2:A6="Acme"), C2:C6)

Last match by date, unsorted data

If rows aren’t in date order, find the max date for that customer first:

=MAXIFS(B2:B6, A2:A6, "Acme")

Pitfalls & errors

"Most recent" assumes date order. search_mode -1 returns the last row, which is only the newest if the data is sorted by date. If not, sort first or use the MAXIFS approach.

The LOOKUP(2,1/…) trick looks odd but is reliable. Dividing 1 by a TRUE/FALSE array makes non-matches errors; LOOKUP ignores errors and returns the last valid (last matching) value.

search_mode is the 6th argument. Don’t confuse it with match_mode (5th). Leave match_mode at 0 and set search_mode to -1.

Practice workbook

📊

Download the free Get the Most Recent (Last) Match practice workbook

The order log with the live last-match (LOOKUP trick), the XLOOKUP search_mode -1 version, and MAXIFS, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I get the last match in Excel?

Use XLOOKUP with search_mode -1: =XLOOKUP(E2, A2:A8, C2:C8, , 0, -1). It searches bottom-to-top, so the last matching row is returned. In older Excel, use =LOOKUP(2, 1/(A2:A8=E2), C2:C8).

How does LOOKUP(2,1/(range=value)) work?

Dividing 1 by the TRUE/FALSE array yields 1 for matches and #DIV/0! for non-matches. LOOKUP searching for 2 (which never exists) walks to the last non-error value, which is the last match.

How do I get the most recent value by date if data isn't sorted?

Use MAXIFS to find the latest date for that key, then look up the value at that date, or sort the data by date first so search_mode -1 returns the newest row.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: XLOOKUP · LOOKUP