How to find the first value greater than a number in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(TRUE,B2:B11>E1,A2:A11)Look for the first TRUE in a comparison: =XLOOKUP(TRUE,B2:B11>E1,A2:A11) returns the date of the first day above the target.
- Result on the example
- 6/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:B11>E1 gives TRUE for every day above 250. XLOOKUP returns the date next to the first TRUE, June 6. June 5 sold exactly 250, which isn't greater than the target, so it doesn't count.
=XLOOKUP(TRUE,B2:B11>E1,A2:A11)Result46179
B2:B1110 rows × 1 col: 182, 205, 199, 238, …A range: 10 cells in one column.
E1250The value in E1.
B2:B11>E1{10 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …The > operator tests greater than.
A2:A1110 rows × 1 col: 6/1/2026, 6/2/2026, 6/3/2026, 6/4/2026, …A range: 10 cells in one column.
XLOOKUP(TRUE,B2:B11>E1,A2:A11)46179XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRUE - lookup_array
B2:B11>E1{10 rows × 1 col} FALSE, FALSE, FALSE, FALSE, … - return_array
A2:A1110 rows × 1 col: 6/1/2026, 6/2/2026, 6/3/2026, 6/4/2026, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TRUE,B2:B11>E1,A2:A11). We ran it in Google Sheets mode on the same data and it returns 6/6/2026.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A11,MATCH(TRUE,INDEX(B2:B11>E1,0),0)) | 6/6/2026 | Every Excel version, without Ctrl+Shift+Enter. |
| =INDEX(FILTER(A2:A11,B2:B11>E1),1) | 6/6/2026 | Filter the days above target and take the first. Excel 2021, Microsoft 365 and Google Sheets. |
Common mistakes
- Decide between
>and>=. With>=the answer here is June 5. - XLOOKUP(E1,B2:B11,A2:A11,,1) is not the same thing: it finds the smallest value above the target anywhere in the list, not the first one in date order.
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 first value greater than a number
E2, return the first date on which units sold were greater than the target in E1.Questions
How do I return the value instead of the date?
Return the units column: =XLOOKUP(TRUE,B2:B11>E1,B2:B11).
How do I find the last day above the target?
Search from the bottom: =XLOOKUP(TRUE,B2:B11>E1,A2:A11,,0,-1).
How do I find the first value below a number?
Flip the comparison: B2:B11<E1.
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.