Look up Name and select data based on overlapping date ranges

[Powderhound Tully]

Hi - this is my first time posting here so I hope I get this right. I've read many posts and find this the best forum for excel answers - helped me build a spreadsheet that does more than one of our software providers reports can provide so kudos to MrExcel.com. I need to get nightly rates for numerous properties where the rates vary depending on time of year as well as day of week. Our weekends are generally higher. Some of the rates are easily gotten because the only criteria is the simple date. However, for easy of creation and understanding, we've historically lumped non-consecutive dates with similar rate pricing together, e.g. late January though mid-March which passes over but excludes Presidents' holiday which have very different rates. This creates the challenges.

My objective is to build a formula that looks up the name of the property in its row, e.g. Property A 101, looks at the column it for a date in Row 15 and looks up the name in a table and looks up the date to pull up the correct Nightly Rate. There are at least three and potentially a fourth challenge to doing that. First, I understand because we've historically lumped non-consecutive data together, we may need to consider how we have represented the data to help get at a better solution, i.e. separate out non-consecutive dates, but that takes away from how we think about the date ranges as one set. I would prefer to NOT change how the data is lumped together and just deal with the other three challenges

As we have really only TWO overlapping periods MLK weekend and Presidents' weekend, I was thinking if we changed the non-consecutive date ranges to include the MLK and Presidents' weekends, we could us a max function if the date satisfied both date ranges criteria for dates that fall within either MLK or Presidents' Weekend. Doing that is the first challenge. The second challenge is if a date falls for example between 01/04/16 and 01/15/16. We need to determine if it is a Friday or Saturday vs Sun-Thurs. to get a rate, that is easy with the Weekday function as Friday & Saturday are >=6. However, for MLK and Presidents' weekend. that won't work as for these two Sunday's the rates are higher. Maybe a test that tests if the date falls within BOTH ranges, then we don't need a day of the week test, but if it doesn't, then have a day of the week test.

Your thoughts and solutions much appreciated.

Excel 2012

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
Rate Table
Rate season name: Winter		Early	Thanksgiving
Nights included in season
Adjustment from Full Season Weekend
Applicable Days of Week
Property A 101
Property A 102
Property A 101
Property A 102

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]Christmas[/TD]
[TD="align: center"]New Years[/TD]
[TD="align: center"]New Years[/TD]
[TD="align: center"]Jan Special[/TD]
[TD="align: center"][/TD]
[TD="align: center"]MLK Wkend[/TD]
[TD="align: center"]Full Season[/TD]
[TD="bgcolor: #FFD966, align: center"]Full Season[/TD]
[TD="align: center"]Presidents Week[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Late Season[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Spring[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]11/1-26 & 12/1-12/18/2014[/TD]
[TD="align: center"]11/27-30/2013[/TD]
[TD="align: center"]12/19-25/2014[/TD]
[TD="align: center"]12/26-30/2014[/TD]
[TD="align: center"]12/31-01/03/2015[/TD]
[TD="align: center"]1/4-16 & 19-22/2015[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1/16-18/2015[/TD]
[TD="align: center"]1/23-2/13, 2/20-3/19/2015[/TD]
[TD="bgcolor: #FFD966, align: center"]Weekend[/TD]
[TD="align: center"]2/13-15/2015[/TD]
[TD="align: center"]2/16-19/2015[/TD]
[TD="align: center"]3/20-4/4/2015[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4/5-30/2015[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]-20%[/TD]
[TD="align: center"]-20%[/TD]
[TD="align: center"]-15%[/TD]
[TD="align: center"]20%[/TD]
[TD="align: center"]20%[/TD]
[TD="align: center"]-20%[/TD]
[TD="align: center"]-5%[/TD]
[TD="align: center"]10%[/TD]
[TD="align: center"]-10%[/TD]
[TD="bgcolor: #FFD966, align: center"]0%[/TD]
[TD="align: center"]5%[/TD]
[TD="align: center"]-5%[/TD]
[TD="align: center"]-10%[/TD]
[TD="align: center"]-20%[/TD]
[TD="align: center"]-20%[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]all[/TD]
[TD="align: center"]all[/TD]
[TD="align: center"]all[/TD]
[TD="align: center"]all[/TD]
[TD="align: center"]all[/TD]
[TD="align: center"]Weekdays Sun-Thurs[/TD]
[TD="align: center"]Fri,Sat[/TD]
[TD="align: center"]all[/TD]
[TD="align: center"]Weekdays Sun-Thurs[/TD]
[TD="bgcolor: #FFD966, align: center"]Fri,Sat[/TD]
[TD="align: center"]Fri,Sat,Sun[/TD]
[TD="align: center"]Weekdays Mon-Thurs[/TD]
[TD="align: center"]Fri,Sat[/TD]
[TD="align: center"]Weekdays Sun-Thurs[/TD]
[TD="align: center"]all[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFD966, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]240[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]220[/TD]
[TD="align: right"]180[/TD]
[TD="bgcolor: #00B0F0, align: right"]200[/TD]
[TD="align: right"]210[/TD]
[TD="align: right"]190[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]160[/TD]
[TD="align: right"]160[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]191.25[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]270[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]213.75[/TD]
[TD="align: right"]247.5[/TD]
[TD="align: right"]202.5[/TD]
[TD="bgcolor: #00B0F0, align: right"]225[/TD]
[TD="align: right"]236.25[/TD]
[TD="align: right"]213.75[/TD]
[TD="align: right"]202.5[/TD]
[TD="align: right"]180[/TD]
[TD="align: right"]180[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"]02/04/15[/TD]
[TD="align: right"]02/05/15[/TD]
[TD="align: right"]02/06/15[/TD]
[TD="align: right"]02/07/15[/TD]
[TD="align: right"]02/08/15[/TD]
[TD="align: right"]02/09/15[/TD]
[TD="align: right"]02/10/15[/TD]
[TD="align: right"]02/11/15[/TD]
[TD="align: right"]02/12/15[/TD]
[TD="align: right"]02/13/15[/TD]
[TD="align: right"]02/14/15[/TD]
[TD="align: right"]02/15/15[/TD]
[TD="align: right"]02/16/15[/TD]
[TD="align: right"]02/17/15[/TD]
[TD="align: right"]02/18/15[/TD]
[TD="align: right"]02/19/15[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C9[/TH]
[TD="align: left"]=(1+C$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D9[/TH]
[TD="align: left"]=(1+D$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E9[/TH]
[TD="align: left"]=(1+E$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F9[/TH]
[TD="align: left"]=(1+F$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G9[/TH]
[TD="align: left"]=(1+G$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H9[/TH]
[TD="align: left"]=(1+H$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I9[/TH]
[TD="align: left"]=(1+I$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J9[/TH]
[TD="align: left"]=(1+J$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]K9[/TH]
[TD="align: left"]=(1+K$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C10[/TH]
[TD="align: left"]=(1+C$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D10[/TH]
[TD="align: left"]=(1+D$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E10[/TH]
[TD="align: left"]=(1+E$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F10[/TH]
[TD="align: left"]=(1+F$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G10[/TH]
[TD="align: left"]=(1+G$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H10[/TH]
[TD="align: left"]=(1+H$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I10[/TH]
[TD="align: left"]=(1+I$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]J10[/TH]
[TD="align: left"]=(1+J$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]K10[/TH]
[TD="align: left"]=(1+K$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]M9[/TH]
[TD="align: left"]=(1+M$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N9[/TH]
[TD="align: left"]=(1+N$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]O9[/TH]
[TD="align: left"]=(1+O$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]P9[/TH]
[TD="align: left"]=(1+P$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Q9[/TH]
[TD="align: left"]=(1+Q$6)*$L9[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]M10[/TH]
[TD="align: left"]=(1+M$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N10[/TH]
[TD="align: left"]=(1+N$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]O10[/TH]
[TD="align: left"]=(1+O$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]P10[/TH]
[TD="align: left"]=(1+P$6)*$L10[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]Q10[/TH]
[TD="align: left"]=(1+Q$6)*$L10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]