Why does VLOOKUP return #N/A when looking up a date?
Excel formula
=XLOOKUP(E1,INT(A2:A8),B2:B8)The column holds dates with times, so the bare date never matches exactly. Strip the time: =XLOOKUP(E1,INT(A2:A8),B2:B8).
- Result on the example
- 7.5 in
E2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
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
A timestamp like 10/14/2026 7:30 AM is the date plus a fraction of a day, 46309.3125, while the date in E1 is the whole number 46309. INT drops the fraction from every timestamp, so the date matches and XLOOKUP returns the first reading that day.
=XLOOKUP(E1,INT(A2:A8),B2:B8)Result7.5
E110/14/2026The value in E1.
A2:A87 rows × 1 col: 10/12/2026 8:24:00, 10/13/2026 14:24:00, 10/14/2026 7:30:00, 10/14/2026 18:00:00, …A range: 7 cells in one column.
INT(A2:A8){7 rows × 1 col} 46307, 46308, 46309, 46309, …INT: Rounds down to the nearest integer.- number
A2:A87 rows × 1 col: 10/12/2026 8:24:00, 10/13/2026 14:24:00, 10/14/2026 7:30:00, 10/14/2026 18:00:00, …
- number
B2:B87 rows × 1 col: 7.2, 7.6, 7.5, 7.9, …A range: 7 cells in one column.
XLOOKUP(E1,INT(A2:A8),B2:B8)7.5XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
E110/14/2026 - lookup_array
INT(A2:A8){7 rows × 1 col} 46307, 46308, 46309, 46309, … - return_array
B2:B87 rows × 1 col: 7.2, 7.6, 7.5, 7.9, …
- lookup_value
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(XLOOKUP(E1,INT(A2:A8),B2:B8)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(B2:B8,MATCH(E1,INT(A2:A8),0)) | 7.5 | For Excel 2019 and earlier. In Excel 2016 and older, confirm it with Ctrl+Shift+Enter. |
| =XLOOKUP(E1,A2:A8,B2:B8,,1) | 7.5 | Match mode 1 finds the first timestamp at or after midnight of the date. No INT needed, but on a date with no readings it returns the next day's. |
Common mistakes
- A date-only format hides the time completely. Switch the column to a date and time format, or check
=A4-INT(A4), to see it. - Dates stored as text never match real dates.
=ISNUMBER(A2)should be TRUE. - Looking up a full timestamp can fail too, since times are rarely exact decimals. Look up the date, or round both sides.
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.
Why does VLOOKUP return #N/A when looking up a date
E2, return the first reading logged on the date in E1. The log holds dates with times.Questions
How do I get the last reading of the day?
Search from the bottom: =XLOOKUP(E1,INT(A2:A8),B2:B8,,0,-1).
How do I total a day's readings?
Use a date range: =SUMIFS(B2:B8,A2:A8,">="&E1,A2:A8,"<"&E1+1).
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.