The formula
Excel 365 / Google SheetsExcelGoogle Sheets
=XLOOKUP(B9,B2:B7,C2:C7,"Not found",0,-1)In E2 → 60,480
Any Excel versionExcel
=LOOKUP(2,1/(B2:B7=B9),C2:C7)In E3 → 60,480
Date of the last matchExcelGoogle Sheets
=XLOOKUP(B9,B2:B7,A2:A7,,0,-1)In E4 → Mar 5, 2026
✓ Tested. All 3 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Ticker | Price | ||
| 2 | Mar 2, 2026 | BTC | 61,200 | 60,480 | |
| 3 | Mar 2, 2026 | ETH | 3400 | 60,480 | |
| 4 | Mar 3, 2026 | BTC | 62,950 | Mar 5, 2026 | |
| 5 | Mar 4, 2026 | ETH | 3525 | ||
| 6 | Mar 5, 2026 | BTC | 60,480 | ||
| 7 | Mar 5, 2026 | SOL | 142 | ||
| 8 | |||||
| 9 | Ticker | BTC |
How it works
- 0
- match_mode 0 = exact match.
- -1
- search_mode -1 = search from last to first, so the bottom-most match wins.
- LOOKUP(2,1/(…))
- 1/(TRUE/FALSE) gives 1 or #DIV/0!. LOOKUP can't find 2, so it returns the last 1, which is the last match.
Common errors and fixes
Why does the formula return #N/A?
No match, or (LOOKUP version) every test is an error. Add XLOOKUP's if_not_found argument.
Not the latest date?
"Last" means lowest row. If your data isn't sorted by date, use MAXIFS to get the latest date first.
Does this work in Google Sheets?
The formulas tagged "Google Sheets" work as-is. Formulas tagged only "Excel" may need ARRAYFORMULA or a different function in Sheets.