Looking Up the Last Matching Value

Most lookups return the first match they find. But sometimes you want the last one: the most recent price for a product, the latest status update, or the final entry for a customer.

When a list grows over time and the same key appears many times, the bottom-most row is usually the freshest. A standard VLOOKUP or MATCH with exact match will stubbornly grab the top row instead.

This lesson shows several reliable ways to pull the last matching value.

MATCH(value, range, 0) scans top to bottom and stops at the very first exact hit. If "Apple" appears in rows 2, 5, and 9, MATCH returns 2.

That is perfect when keys are unique, but it ignores newer rows. To reach the last occurrence we need a technique that searches from the bottom or that returns the position of the final match.

If you have a modern version of Excel or Google Sheets, XLOOKUP makes this easy. Its fifth and sixth arguments control match mode and search direction.

Pass -1 as the search-mode argument to search last to first. XLOOKUP then returns the value tied to the bottom-most matching key.

Here it looks up the product in G1 against A2:A10 and returns the matching price from B2:B10, starting its search at the bottom.

On older spreadsheets, a well-known trick uses LOOKUP with the number 2 and a clever divide-by-condition.

The expression 1/(A2:A10=G1) produces 1 for matching rows and a divide error for non-matches. LOOKUP searching for 2 (a value larger than any present) glides past the errors and lands on the last valid 1, returning the matching value from B2:B10.

Step through 1/(A2:A10=G1):

• Rows where the key matches give 1/TRUE = 1.
• Rows that do not match give 1/FALSE = a #DIV/0! error.

LOOKUP ignores errors and, when it cannot find its target (2), returns the result aligned with the last non-error entry. Since all matches are 1, the last 1 wins, so you get the last matching row's value.

You can also stay in the INDEX-MATCH family. The idea is to find the position of the last match, then feed it to INDEX.

Using the same divide trick inside MATCH, search for 2 against 1/(A2:A10=G1) to get the row position of the final match. Hand that position to INDEX over the return column.

When the third argument of MATCH is omitted it defaults to 1, meaning approximate match on ascending data. MATCH then looks for the largest value that is less than or equal to 2.

The array 1/(A2:A10=G1) contains only 1s and errors. The largest value at or below 2 is 1, and MATCH returns the position of the last such 1. That position is exactly the last matching row.

Suppose A2:A10 lists order statuses for "Order-7" recorded over time, and B2:B10 holds the status text. "Order-7" appears in rows 3, 6, and 9.

• The match array marks rows 3, 6, 9 with a 1 and the rest as errors.
• MATCH(2, ...) returns 9 as the position (counting from the start of the range), the last match.
• INDEX returns the status from that final row, the most recent one.

Which approach should you use?

• XLOOKUP with -1: cleanest and most readable if your app supports it.
• LOOKUP(2, 1/...): works almost everywhere, no special version needed.
• INDEX-MATCH(2, 1/...): handy when you also need the position or want to return from a different column.

All three give the same answer; pick based on your tools and how readable you want the formula to be.

Watch out for these issues:

• Mismatched range sizes: the condition range and the return range must be the same height, or rows misalign.
• Hidden duplicates: trailing spaces can make "Apple " differ from "Apple"; clean text first with TRIM.
• No match at all: the trick returns an error if nothing matches. Wrap it in IFERROR for a friendly fallback.

You can combine the last-match trick with two conditions. Multiply the condition tests inside the divide, so only rows meeting both keys produce a 1.

For instance, find the most recent price where product equals G1 and region equals G2. The LOOKUP(2, ...) trick still lands on the last qualifying row.

This is handy for time-stamped logs where the same product appears in several regions.

Check your grasp of last-match lookups.

To return the last matching value instead of the first:

• Use XLOOKUP(..., -1) to search bottom to top where available.
• Use the classic LOOKUP(2, 1/(range=key), result) trick on any version.
• Use INDEX(result, MATCH(2, 1/(range=key))) when you need the position too.

Remember to keep ranges the same size, clean stray spaces, and wrap the formula in IFERROR for safety.