# How to look up the weekly column for a date in Excel

Match the date to the week starting on or before it, then pick the person's row: `=XLOOKUP(J2,A2:A5,XLOOKUP(J1,B1:G1,B2:G5,,-1))`.

**Excel:** `=XLOOKUP(J2,A2:A5,XLOOKUP(J1,B1:G1,B2:G5,,-1))` (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 | G | H | I | J |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Person | 2026-09-07 | 2026-09-14 | 2026-09-21 | 2026-09-28 | 2026-10-05 | 2026-10-12 |  | Date | 2026-09-24 |
| Ana | 32 | 40 | 36 | 40 | 24 | 40 |  | Person | Cara |
| Ben | 40 | 40 | 40 | 32 | 40 | 16 |  | Planned hours |  |
| Cara | 24 | 32 | 36 | 40 | 36 | 40 |  |  |  |
| Dev | 40 | 16 | 0 | 40 | 40 | 40 |  |  |  |

Formula in J3: `=XLOOKUP(J2,A2:A5,XLOOKUP(J1,B1:G1,B2:G5,,-1))`

Result: 36

## How it works

The headers are real dates. The inner XLOOKUP looks for Thursday, September 24 in the header row with match mode `-1`, so it falls back to the week starting Monday, September 21, and returns that whole column. The outer XLOOKUP picks Cara's row from it: 36 hours.

## Other ways

- `=INDEX(B2:G5,MATCH(J2,A2:A5,0),MATCH(J1,B1:G1,1))`: INDEX with an exact MATCH for the person and an approximate MATCH for the week. Works in every version; the dates must run left to right.

## Common mistakes

- Headers typed as text, like "Sep 21", don't compare as dates. Use real dates and format them to look the way you want.
- A date before the first week returns #N/A.

## Questions

**What if the columns are months?**

The same formula works with first-of-month dates as headers.

**How do I find the Monday of a date's week?**

Use `=J1-WEEKDAY(J1,3)`, which steps back to Monday.

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/weekly-column-for-date