How to find the last occurrence of a value in Excel

Q: How do I find the last non-blank value in a column?

Use =LOOKUP(2,1/(A2:A100<>""),A2:A100).

Q: How do I return the last amount instead of the date?

Change the return range: =XLOOKUP(F1,B2:B8,C2:C8,,0,-1).

Formula (Excel and Google Sheets)

=XLOOKUP(F1,B2:B8,A2:A8,,0,-1)

Use XLOOKUP with search mode -1 to search from the bottom up: =XLOOKUP(value, lookup_range, return_range,,0,-1).

Result on the example: 6/6/2026 in F2
Works in: Excel 2021, 2024 and Microsoft 365
Google Sheets: Same formula

See it work

The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.

How it works

XLOOKUP normally returns the first match from the top. Its sixth argument, the search mode, set to -1 searches from the last row up, so the first match it finds is the last one in the list. The empty fifth argument keeps the default "not found" behavior and 0 asks for an exact match.

=XLOOKUP(F1,B2:B8,A2:A8,,0,-1)

Result46179

F1"Contoso"
The value in F1.
B2:B87 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …
A range: 7 cells in one column.
A2:A87 rows × 1 col: 5/2/2026, 5/9/2026, 5/16/2026, 5/23/2026, …
A range: 7 cells in one column.
-1-1
Makes the value negative.
XLOOKUP(F1,B2:B8,A2:A8,,0,-1)46179
XLOOKUP: Searches one range and returns the matching item from another.
- lookup_valueF1"Contoso"
- lookup_arrayB2:B87 rows × 1 col: "Contoso", "Fabrikam", "Contoso", "Northwind", …
- return_arrayA2:A87 rows × 1 col: 5/2/2026, 5/9/2026, 5/16/2026, 5/23/2026, …
- if_not_found(omitted)
- match_mode0
- search_mode-1-1

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(F1,B2:B8,A2:A8,,0,-1). We ran it in Google Sheets mode on the same data and it returns 6/6/2026.

Other ways to do it

Each one was run on the same example sheet.

Formula	Result	When to use it
=INDEX(A2:A8,MAX((B2:B8=F1)*(ROW(B2:B8)-ROW(B1))))	6/6/2026	Works in older versions: the test times each row's position gives the position of every match, and MAX picks the last one.
=MAXIFS(A2:A8,B2:B8,F1)	6/6/2026	When "last" means the latest date, MAXIFS answers directly, even if the list isn't sorted.

Common mistakes

Searching from the bottom gives the last row, not the latest date. If the list isn't sorted by date, use MAXIFS.
The return range needs date formatting, or the result shows as a number like 46179.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Find the last occurrence of a value

In F2, return the date of the most recent order for the customer in F1. The list is in date order.

Questions

How do I find the last non-blank value in a column?

Use =LOOKUP(2,1/(A2:A100<>""),A2:A100).

How do I return the last amount instead of the date?

Change the return range: =XLOOKUP(F1,B2:B8,C2:C8,,0,-1).

Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.