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

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

**Excel:** `=INDEX($F$2:$F$8,WEEKDAY(A2,2))` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Booking date | Rate |  |  | Day | Nightly rate |
| 2026-08-03 |  |  |  | Mon | 129 |
| 2026-08-07 |  |  |  | Tue | 129 |
| 2026-08-08 |  |  |  | Wed | 135 |
| 2026-08-11 |  |  |  | Thu | 145 |
| 2026-08-13 |  |  |  | Fri | 189 |
| 2026-08-16 |  |  |  | Sat | 209 |
|  |  |  |  | Sun | 159 |

Formula in B2, filled to B7: `=INDEX($F$2:$F$8,WEEKDAY(A2,2))`

Result: $129; $189; $209; $129; $145; $159

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

## Other ways

- `=XLOOKUP(TEXT(A2,"ddd"),$E$2:$E$8,$F$2:$F$8)`: 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)`: 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.

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

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/rate-by-day-of-week