Spreadsheet Practice

How to look up a rate based on the day of the week in Excel

Formula (Excel and Google Sheets)

=INDEX($F$2:$F$8,WEEKDAY(A2,2))

Use the weekday number as the position in a Monday-to-Sunday rate table: =INDEX($F$2:$F$8,WEEKDAY(A2,2)).

Result on the example
one result per row in B2, filled down to B7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

How it works

WEEKDAY(A2,2) numbers the days from Monday = 1 to Sunday = 7, which is exactly the order of the rate table. INDEX returns that row: August 7, 2026 is a Friday, day 5, so the rate is $189. The weekday number doesn't depend on the language of the day names.

=INDEX($F$2:$F$8,WEEKDAY(A2,2))

Result129

  1. $F$2:$F$87 rows × 1 col: $129, $129, $135, $145, …

    A range: 7 cells in one column.

  2. A28/3/2026

    The value in A2.

  3. WEEKDAY(A2,2)1

    WEEKDAY: Day of the week as a number; Sunday = 1 by default.

    • serial_numberA28/3/2026
    • return_type2
  4. INDEX($F$2:$F$8,WEEKDAY(A2,2))129

    INDEX: Value at a given row and column of a range.

    • array$F$2:$F$87 rows × 1 col: $129, $129, $135, $145, …
    • row_numWEEKDAY(A2,2)1

In Google Sheets

The same formula works in Google Sheets: =INDEX($F$2:$F$8,WEEKDAY(A2,2)). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=XLOOKUP(TEXT(A2,"ddd"),$E$2:$E$8,$F$2:$F$8)$129, $189, $209…Looks up the day's short name. Simple to read, but TEXT returns names in the language of your settings.
=CHOOSE(WEEKDAY(A2,2),$F$2,$F$3,$F$4,$F$5,$F$6,$F$7,$F$8)$129, $189, $209…CHOOSE with one cell per weekday; handy when the rates sit in scattered cells.

Common mistakes

  • WEEKDAY without its second argument starts at Sunday = 1, which shifts every rate by a day for a Monday-first table.
  • The table must have all seven days, in order, for the INDEX version.

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 rate based on the day of the week

In B2, return the nightly rate for the booking date in A2 using the rate for its day of the week in E2:F8. Fill down to B7.

Questions

How do I price a stay of several nights?

Add one rate per night: =SUMPRODUCT(INDEX($F$2:$F$8,WEEKDAY(A2+SEQUENCE(C2,1,0),2))) for C2 nights from A2.

How do I use one rate for weekdays and another for weekends?

Use IF: =IF(WEEKDAY(A2,2)>5,weekend_rate,weekday_rate).

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.