Domenic,

I used your formula shown below and it work whilst the booking date is in
one season, but when the season changes mid booking the formula is not
changing over to the different rate table.

eg; a 5 night booking starting 30 nov and ending 5 dec crosses 2 seasons.

date: 30/11 1/12 2/12 3/12 4/12
season: low high high high high
price: 110 150 150 150 165

Totals = 725 your result is 565

each day of the booking needs to be checked for the season as this changes
and effects the totals.

"Domenic" wrote:

For simplicity, let's assume the following...

A1:H3 contains the table for the low season

A5:H7 contains the table for the mid season

A9:H11 contains the table for the high season

J1:J365 contains the date for each day of the year

K1:K365 contains the corresponding season (Low, Mid, or HIgh)

M1 contains the date of interest, such as 8/26/2009

N1 contains the number of nights, such as 5

O1 contains the unit number, such as Unit 2

First, define the following...

Insert Name Define

Name: Low

Refers to: =$A$1:$H$3

Click Add

Name: Mid

Refers to: =$A$5:$H$7

Click Add

Name: High

Refers to: =$A$9:$H$11

Click Ok

Then try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

=SUM(VLOOKUP(O1,CHOOSE(MATCH(VLOOKUP(M1,J1:K365,2, 0),{"Low","Mid","High"}
,0),Low,Mid,High),WEEKDAY(M1+ROW(INDIRECT("1:"&N1) )-1)+1,0))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions

In article ,
nba wrote:

I have 2 sheets. One with 365 dates showing low, mid or high season. The
second sheet has 3 tables listing multiple units with different pricing for
each day of the week for each season. EG:

table 2 rates low season
sun mon tue wed thu fri sat
unit 1 100 90 90 90 90 100 100
unit 2 150 100 100 100 100 150 150

I am using
VLOOKUP(H9,INDIRECT(VLOOKUP(F11,Dates,2,FALSE)),WE EKDAY(D11)+2,FALSE) to find
the right rate for the unit (H9) for the right date but now i need to add
each night of the booking for example 5 nights starting wed in unit 1.

that would be 90+90+100+100+100=480

can you help.