How to find the last match with multiple criteria in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(1,(B2:B11=G1)*(C2:C11=G2),A2:A11,"None",0,-1)Multiply the tests and search from the bottom: =XLOOKUP(1,(B2:B11=G1)*(C2:C11=G2),A2:A11,"None",0,-1).
- Result on the example
- 3/5/2026 in
G3 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in G3. Change any input and the result updates, or edit the formula to experiment.
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.
=XLOOKUP(1,(B2:B11=G1)*(C2:C11=G2),A2:A11,"None",0,-1)Result46086
B2:B1110 rows × 1 col: "Kim", "Ruiz", "Kim", "Ruiz", …A range: 10 cells in one column.
G1"Kim"The value in G1.
B2:B11=G1{10 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
C2:C1110 rows × 1 col: "North", "South", "South", "North", …A range: 10 cells in one column.
G2"South"The value in G2.
C2:C11=G2{10 rows × 1 col} FALSE, TRUE, TRUE, FALSE, …The = operator tests whether the two sides are equal.
B2:B11=G1)*(C2:C11=G2{10 rows × 1 col} 0, 0, 1, 0, …The * operator multiplies.
A2:A1110 rows × 1 col: 3/2/2026, 3/2/2026, 3/3/2026, 3/4/2026, …A range: 10 cells in one column.
-1-1Makes the value negative.
XLOOKUP(1,(B2:B11=G1)*(C2:C11=G2),A2:A11,"None",0,-1)46086XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
1 - lookup_array
B2:B11=G1)*(C2:C11=G2{10 rows × 1 col} 0, 0, 1, 0, … - return_array
A2:A1110 rows × 1 col: 3/2/2026, 3/2/2026, 3/3/2026, 3/4/2026, … - if_not_found
"None" - match_mode
0 - search_mode
-1-1
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(1,(B2:B11=G1)*(C2:C11=G2),A2:A11,"None",0,-1). We ran it in Google Sheets mode on the same data and it returns 3/5/2026.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A11,MAX((B2:B11=G1)*(C2:C11=G2)*(ROW(A2:A11)-ROW(A2)+1))) | 3/5/2026 | 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) | 3/5/2026 | 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.
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 match with multiple criteria
G3, return the date of the last run by the driver in G1 on the route in G2.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.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.