How to find the last date a value was above a target in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(TRUE,B2:B10>E1,A2:A10,"Never",0,-1)Search from the bottom for the first value above the target: =XLOOKUP(TRUE,B2:B10>E1,A2:A10,"Never",0,-1).
- Result on the example
- 2/6/2026 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
How it works
B2:B10>E1 is TRUE on every day the close beat $150. Search mode -1 makes XLOOKUP read from the bottom up, so the first TRUE it meets is the latest one: February 6. February 11 closed at exactly $150, which isn't above it.
=XLOOKUP(TRUE,B2:B10>E1,A2:A10,"Never",0,-1)Result46059
B2:B109 rows × 1 col: $148.20, $151.75, $153.10, $149.60, …A range: 9 cells in one column.
E1$150.00The value in E1.
B2:B10>E1{9 rows × 1 col} FALSE, TRUE, TRUE, FALSE, …The > operator tests greater than.
A2:A109 rows × 1 col: 2/2/2026, 2/3/2026, 2/4/2026, 2/5/2026, …A range: 9 cells in one column.
-1-1Makes the value negative.
XLOOKUP(TRUE,B2:B10>E1,A2:A10,"Never",0,-1)46059XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRUE - lookup_array
B2:B10>E1{9 rows × 1 col} FALSE, TRUE, TRUE, FALSE, … - return_array
A2:A109 rows × 1 col: 2/2/2026, 2/3/2026, 2/4/2026, 2/5/2026, … - if_not_found
"Never" - match_mode
0 - search_mode
-1-1
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TRUE,B2:B10>E1,A2:A10,"Never",0,-1). We ran it in Google Sheets mode on the same data and it returns 2/6/2026.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =MAXIFS(A2:A10,B2:B10,">"&E1) | 2/6/2026 | The latest date among days above the level, even if the list isn't sorted. Returns 0 if there are none. |
| =INDEX(A2:A10,MAX((B2:B10>E1)*(ROW(A2:A10)-ROW(A2)+1))) | 2/6/2026 | Every version (Ctrl+Shift+Enter in Excel 2019 and earlier). |
Common mistakes
- Searching from the bottom assumes the dates run oldest to newest. Use MAXIFS if they don't.
- Use
>=if a close exactly at the level should count; then the answer is February 11.
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 date a value was above a target
E2, return the most recent date on which the close was above the level in E1.Questions
How many days ago was that?
Subtract it from the latest date: =MAX(A2:A10)-E2.
How do I find the first date above the level?
Leave out the search mode, so XLOOKUP reads 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.