Why a lookup returns a number instead of a date
Formula (Excel and Google Sheets)
=XLOOKUP(F1,A2:A8,C2:C8)The lookup is right; the result cell just isn't formatted as a date. Excel stores dates as numbers, so give the cell a date format, or wrap the result in TEXT for a label.
- Result on the example
- 12/4/2026 in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
Excel stores a date as the number of days since January 1, 1900: December 4, 2026 is 46360. A lookup copies the number but not the source cell's format, so a General cell shows 46360. Formatting F2 as a date (Ctrl+Shift+3, or Format Cells) shows it as 12/4/2026 and keeps it usable in date math.
=XLOOKUP(F1,A2:A8,C2:C8)Result46360
F1"Website redesign"The value in F1.
A2:A87 rows × 1 col: "CRM migration", "Website redesign", "Brand refresh", "Data warehouse", …A range: 7 cells in one column.
C2:C87 rows × 1 col: 11/20/2026, 12/4/2026, 1/15/2027, 2/26/2027, …A range: 7 cells in one column.
XLOOKUP(F1,A2:A8,C2:C8)46360XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
F1"Website redesign" - lookup_array
A2:A87 rows × 1 col: "CRM migration", "Website redesign", "Brand refresh", "Data warehouse", … - return_array
C2:C87 rows × 1 col: 11/20/2026, 12/4/2026, 1/15/2027, 2/26/2027, …
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(F1,A2:A8,C2:C8). We ran it in Google Sheets mode on the same data and it returns 12/4/2026.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =TEXT(XLOOKUP(F1,A2:A8,C2:C8),"mmm d, yyyy") | Dec 4, 2026Different result | Use TEXT when the date goes into a sentence or label. The result is text, so you can't calculate with it. Text: "Dec 4, 2026". |
| =INDEX(C2:C8,MATCH(F1,A2:A8,0)) | 12/4/2026 | INDEX/MATCH has the same behavior; it's the cell format that matters. |
Common mistakes
- A result of 1/0/1900 means the lookup returned 0: the date cell in the table is empty.
- Spilled results take the format of the cells they land in, so format the whole spill area as dates.
- TEXT results look like dates but sort and compare as text.
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 a lookup returns a number instead of a date
F2, return the due date of the project in F1. The cell is formatted as a date, so it shows as one.Questions
How do I join the date into a sentence?
Use TEXT: ="Due "&TEXT(XLOOKUP(F1,A2:A8,C2:C8),"mmm d").
How do I format the cell as a date in Google Sheets?
Use Format > Number > Date.
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.