# How to find the last date a value was above a target in Excel

Search from the bottom for the first value above the target: `=XLOOKUP(TRUE,B2:B10>E1,A2:A10,"Never",0,-1)`.

**Excel:** `=XLOOKUP(TRUE,B2:B10>E1,A2:A10,"Never",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 |
| --- | --- | --- | --- | --- |
| Date | Close |  | Level | 150 |
| 2026-02-02 | 148.2 |  | Last close above level |  |
| 2026-02-03 | 151.75 |  |  |  |
| 2026-02-04 | 153.1 |  |  |  |
| 2026-02-05 | 149.6 |  |  |  |
| 2026-02-06 | 152.4 |  |  |  |
| 2026-02-09 | 147.9 |  |  |  |
| 2026-02-10 | 146.35 |  |  |  |
| 2026-02-11 | 150 |  |  |  |
| 2026-02-12 | 148.8 |  |  |  |

Formula in E2: `=XLOOKUP(TRUE,B2:B10>E1,A2:A10,"Never",0,-1)`

Result: 2/6/2026

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

## Other ways

- `=MAXIFS(A2:A10,B2:B10,">"&E1)`: 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)))`: 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.

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

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-date-above-target