# Why does VLOOKUP return #N/A when looking up a date?

The column holds dates with times, so the bare date never matches exactly. Strip the time: =XLOOKUP(E1,INT(A2:A8),B2:B8).

**Excel:** `=XLOOKUP(E1,INT(A2:A8),B2:B8)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=ARRAYFORMULA(XLOOKUP(E1,INT(A2:A8),B2:B8))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Logged at | Reading |  | Date | 2026-10-14 |
| 46307.35 | 7.2 |  | First reading |  |
| 46308.6 | 7.6 |  |  |  |
| 46309.3125 | 7.5 |  |  |  |
| 46309.75 | 7.9 |  |  |  |
| 46310.5 | 8.1 |  |  |  |
| 46311.4 | 7.7 |  |  |  |
| 46311.8 | 7.3 |  |  |  |

Formula in E2: `=XLOOKUP(E1,INT(A2:A8),B2:B8)`

Result: 7.5

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

## Other ways

- `=INDEX(B2:B8,MATCH(E1,INT(A2:A8),0))`: For Excel 2019 and earlier. In Excel 2016 and older, confirm it with Ctrl+Shift+Enter.
- `=XLOOKUP(E1,A2:A8,B2:B8,,1)`: 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.

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

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/lookup-date-with-time-returns-na