I fwouold use two sumproducts. I'll put rows and column into your data to
show you how

A B C D E F G H
1 sun mon tue wed thu fri sat
2 unit 1 100 90 90 90 90 100 100
3 unit 2 150 100 100 100 100 150 150


find the minum rates from row 2 comparing with row 3

=Sumproduct(--(B2:H2<=B3:H3),B2:H2)

now the same in 2nd row. I'm only using less than

=Sumproduct(--(B3:H3<B2:H2),B3:H3)

Now put the two formulas into one
=Sumproduct(--(B2:H2<=B3:H3),B2:H2) + Sumproduct(--(B3:H3<B2:H2),B3:H3)



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