# How to find the last occurrence of a value in Excel

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

**Excel:** `=XLOOKUP(F1,B2:B8,A2:A8,,0,-1)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Date | Customer | Amount |  | Customer | Contoso |
| 2026-05-02 | Contoso | 1200 |  | Last order |  |
| 2026-05-09 | Fabrikam | 640 |  |  |  |
| 2026-05-16 | Contoso | 980 |  |  |  |
| 2026-05-23 | Northwind | 310 |  |  |  |
| 2026-05-30 | Fabrikam | 455 |  |  |  |
| 2026-06-06 | Contoso | 1475 |  |  |  |
| 2026-06-13 | Northwind | 820 |  |  |  |

Formula in F2: `=XLOOKUP(F1,B2:B8,A2:A8,,0,-1)`

Result: 6/6/2026

## 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.

## Other ways

- `=INDEX(A2:A8,MAX((B2:B8=F1)*(ROW(B2:B8)-ROW(B1))))`: 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)`: 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.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/find-last-match