How to look up a date when dates are stored as text
Formula (Excel and Google Sheets)
=XLOOKUP(DATEVALUE(E1),A2:A8,B2:B8)Convert the text to a real date inside the lookup: =XLOOKUP(DATEVALUE(E1),A2:A8,B2:B8).
- Result on the example
- $4,560 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
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.
=XLOOKUP(DATEVALUE(E1),A2:A8,B2:B8)Result4560
E1"3/18/2026"The value in E1.
DATEVALUE(E1)3/18/2026 (46099)DATEVALUE: Converts date text to a date.- date_text
E1"3/18/2026"
- date_text
A2:A87 rows × 1 col: 3/16/2026, 3/17/2026, 3/18/2026, 3/19/2026, …A range: 7 cells in one column.
B2:B87 rows × 1 col: $4,210, $3,875, $4,560, $4,395, …A range: 7 cells in one column.
XLOOKUP(DATEVALUE(E1),A2:A8,B2:B8)4560XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
DATEVALUE(E1)3/18/2026 (46099) - lookup_array
A2:A87 rows × 1 col: 3/16/2026, 3/17/2026, 3/18/2026, 3/19/2026, … - return_array
B2:B87 rows × 1 col: $4,210, $3,875, $4,560, $4,395, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(DATEVALUE(E1),A2:A8,B2:B8). We ran it in Google Sheets mode on the same data and it returns $4,560.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =XLOOKUP(--E1,A2:A8,B2:B8) | $4,560 | The double minus also converts date-like text to a number, in one keystroke less. |
| =VLOOKUP(DATEVALUE(E1),A2:B8,2,FALSE) | $4,560 | 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.
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.
Look up a date when dates are stored as text
E1 was pasted as text. In E2, return the sales for that date from the real dates in column A.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.
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.