Spreadsheet Practice

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

Formula (Excel and Google Sheets)

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

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

Result on the example
36 in J3
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in J3. Change any input and the result updates, or edit the formula to experiment.

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.

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

Result36

  1. J2"Cara"

    The value in J2.

  2. A2:A54 rows × 1 col: "Ana", "Ben", "Cara", "Dev"

    A range: 4 cells in one column.

  3. J19/24/2026

    The value in J1.

  4. B1:G11 row × 6 cols: 9/7/2026, 9/14/2026, 9/21/2026, 9/28/2026, …

    A range: 6 cells in one row.

  5. B2:G54 rows × 6 cols: 32, 40, 36, 40, …

    A range: 4 rows × 6 columns.

  6. -1-1

    Makes the value negative.

  7. XLOOKUP(J1,B1:G1,B2:G5,,-1){4 rows × 1 col} 36, 40, 36, 0

    XLOOKUP: Searches one range and returns the matching item from another.

    • lookup_valueJ19/24/2026
    • lookup_arrayB1:G11 row × 6 cols: 9/7/2026, 9/14/2026, 9/21/2026, 9/28/2026, …
    • return_arrayB2:G54 rows × 6 cols: 32, 40, 36, 40, …
    • if_not_found(omitted)
    • match_mode-1-1
  8. XLOOKUP(J2,A2:A5,XLOOKUP(J1,B1:G1,B2:G5,,-1))36

    XLOOKUP: Searches one range and returns the matching item from another.

    • lookup_valueJ2"Cara"
    • lookup_arrayA2:A54 rows × 1 col: "Ana", "Ben", "Cara", "Dev"
    • return_arrayXLOOKUP(J1,B1:G1,B2:G5,,-1){4 rows × 1 col} 36, 40, 36, 0

In Google Sheets

The same formula works in Google Sheets: =XLOOKUP(J2,A2:A5,XLOOKUP(J1,B1:G1,B2:G5,,-1)). We ran it in Google Sheets mode on the same data and it returns 36.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=INDEX(B2:G5,MATCH(J2,A2:A5,0),MATCH(J1,B1:G1,1))36INDEX 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.

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

Each column is a week, headed by its Monday. In J3, return the planned hours for the person in J2 in the week that contains the date in J1.

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.

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.