# How to find the last match with multiple criteria in Excel

Multiply the tests and search from the bottom: `=XLOOKUP(1,(B2:B11=G1)*(C2:C11=G2),A2:A11,"None",0,-1)`.

**Excel:** `=XLOOKUP(1,(B2:B11=G1)*(C2:C11=G2),A2:A11,"None",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 | G |
| --- | --- | --- | --- | --- | --- | --- |
| Date | Driver | Route | Stops |  | Driver | Kim |
| 2026-03-02 | Kim | North | 14 |  | Route | South |
| 2026-03-02 | Ruiz | South | 11 |  | Last run |  |
| 2026-03-03 | Kim | South | 9 |  |  |  |
| 2026-03-04 | Ruiz | North | 12 |  |  |  |
| 2026-03-05 | Kim | South | 10 |  |  |  |
| 2026-03-06 | Patel | South | 13 |  |  |  |
| 2026-03-09 | Kim | North | 15 |  |  |  |
| 2026-03-10 | Ruiz | South | 8 |  |  |  |
| 2026-03-11 | Patel | North | 12 |  |  |  |
| 2026-03-12 | Kim | North | 16 |  |  |  |

Formula in G3: `=XLOOKUP(1,(B2:B11=G1)*(C2:C11=G2),A2:A11,"None",0,-1)`

Result: 3/5/2026

## How it works

The multiplied tests are 1 only on rows where both the driver and the route match: March 3 and March 5. Search mode `-1` makes XLOOKUP start at the bottom, so the first 1 it finds is the last run, March 5.

## Other ways

- `=INDEX(A2:A11,MAX((B2:B11=G1)*(C2:C11=G2)*(ROW(A2:A11)-ROW(A2)+1)))`: Every Excel version (Ctrl+Shift+Enter in 2019 and earlier): MAX picks the position of the last matching row.
- `=MAXIFS(A2:A11,B2:B11,G1,C2:C11,G2)`: When "last" means the latest date, MAXIFS answers directly even if the log isn't sorted.

## Common mistakes

- Searching from the bottom gives the last row, not the latest date. If the log isn't in date order, use MAXIFS.
- In the INDEX/MAX version, no match makes MAX return 0 and INDEX return the whole column. Use XLOOKUP's "None" or wrap it in IF.

## Questions

**How do I return the stops on that run?**

Change the return range: `=XLOOKUP(1,(B2:B11=G1)*(C2:C11=G2),D2:D11,"None",0,-1)`.

**How do I get the first match instead?**

Leave out the last two arguments, so XLOOKUP searches from the top.

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

Source: https://spreadsheetpractice.com/formulas/last-match-with-multiple-criteria