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.