# Why a lookup returns a number instead of a date

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.

**Excel:** `=XLOOKUP(F1,A2:A8,C2:C8)` (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 | F |
| --- | --- | --- | --- | --- | --- |
| Project | Owner | Due date |  | Project | Website redesign |
| CRM migration | Lena Park | 2026-11-20 |  | Due date |  |
| Website redesign | Omar Ruiz | 2026-12-04 |  |  |  |
| Brand refresh | Noor Haddad | 2027-01-15 |  |  |  |
| Data warehouse | Tom Becker | 2027-02-26 |  |  |  |
| Mobile app | Priya Shah | 2027-03-31 |  |  |  |
| Security audit | Ines Lopez | 2026-11-06 |  |  |  |
| Office move | Sam Okafor | 2027-01-08 |  |  |  |

Formula in F2: `=XLOOKUP(F1,A2:A8,C2:C8)`

Result: 12/4/2026

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

## Other ways

- `=TEXT(XLOOKUP(F1,A2:A8,C2:C8),"mmm d, yyyy")`: 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))`: 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.

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

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-returns-number-instead-of-date