# How to look up a date when dates are stored as text

Convert the text to a real date inside the lookup: `=XLOOKUP(DATEVALUE(E1),A2:A8,B2:B8)`.

**Excel:** `=XLOOKUP(DATEVALUE(E1),A2:A8,B2:B8)` (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 | Sales |  | Date (as text) | 3/18/2026 |
| 2026-03-16 | 4210 |  | Sales |  |
| 2026-03-17 | 3875 |  |  |  |
| 2026-03-18 | 4560 |  |  |  |
| 2026-03-19 | 4395 |  |  |  |
| 2026-03-20 | 5120 |  |  |  |
| 2026-03-21 | 6240 |  |  |  |
| 2026-03-22 | 5980 |  |  |  |

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

Result: $4,560

## How it works

The dates in column A are numbers underneath (March 18, 2026 is 46099), while "3/18/2026" in E1 is text, so they never match. DATEVALUE reads the text as a date and returns the same number, and the lookup finds it: $4,560.

## Other ways

- `=XLOOKUP(--E1,A2:A8,B2:B8)`: The double minus also converts date-like text to a number, in one keystroke less.
- `=VLOOKUP(DATEVALUE(E1),A2:B8,2,FALSE)`: The same fix with VLOOKUP, for every version.

## Common mistakes

- DATEVALUE reads the text in your computer's date order. "3/18/2026" works with US settings; with day-first settings it returns #VALUE!.
- If the table holds the text dates instead, convert the table side: `XLOOKUP(E1,DATEVALUE(A2:A8),B2:B8)` with a real date in E1.
- Dates that include times don't match a plain date. Strip the time with INT.

## Questions

**How do I tell whether a date is text?**

Use `=ISTEXT(E1)`. Text dates also usually sit on the left of the cell.

**How do I convert a whole column of text dates?**

Use Data > Text to Columns with the Date option, or a helper column with DATEVALUE and paste values.

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-dates-stored-as-text