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
1	Rate Table
2
3
4	Rate season name: Winter		Early	Thanksgiving	Christmas	New Years	New Years	Jan Special		MLK Wkend	Full Season	Full Season	Presidents Week		Late Season		Spring
5	Nights included in season		11/1-26 & 12/1-12/18/2014	11/27-30/2013	12/19-25/2014	12/26-30/2014	12/31-01/03/2015	1/4-16 & 19-22/2015		1/16-18/2015	1/23-2/13, 2/20-3/19/2015	Weekend	2/13-15/2015	2/16-19/2015	3/20-4/4/2015		4/5-30/2015
6	Adjustment from Full Season Weekend		-20%	-20%	-15%	20%	20%	-20%	-5%	10%	-10%	0%	5%	-5%	-10%	-20%	-20%
7	Applicable Days of Week		all	all	all	all	all	Weekdays Sun-Thurs	Fri,Sat	all	Weekdays Sun-Thurs	Fri,Sat	Fri,Sat,Sun	Weekdays Mon-Thurs	Fri,Sat	Weekdays Sun-Thurs	all
8
9	Property A 101		160	160	170	240	240	160	190	220	180	200	210	190	180	160	160
10	Property A 102		180	180	191.25	270	270	180	213.75	247.5	202.5	225	236.25	213.75	202.5	180	180
11
12
13
14
15		02/04/15	02/05/15	02/06/15	02/07/15	02/08/15	02/09/15	02/10/15	02/11/15	02/12/15	02/13/15	02/14/15	02/15/15	02/16/15	02/17/15	02/18/15	02/19/15
16	Property A 101
17	Property A 102

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula C9 =(1+C$6)*$L9 D9 =(1+D$6)*$L9 E9 =(1+E$6)*$L9 F9 =(1+F$6)*$L9 G9 =(1+G$6)*$L9 H9 =(1+H$6)*$L9 I9 =(1+I$6)*$L9 J9 =(1+J$6)*$L9 K9 =(1+K$6)*$L9 C10 =(1+C$6)*$L10 D10 =(1+D$6)*$L10 E10 =(1+E$6)*$L10 F10 =(1+F$6)*$L10 G10 =(1+G$6)*$L10 H10 =(1+H$6)*$L10 I10 =(1+I$6)*$L10 J10 =(1+J$6)*$L10 K10 =(1+K$6)*$L10 M9 =(1+M$6)*$L9 N9 =(1+N$6)*$L9 O9 =(1+O$6)*$L9 P9 =(1+P$6)*$L9 Q9 =(1+Q$6)*$L9 M10 =(1+M$6)*$L10 N10 =(1+N$6)*$L10 O10 =(1+O$6)*$L10 P10 =(1+P$6)*$L10 Q10 =(1+Q$6)*$L10 <tbody> </tbody>

<tbody>
</tbody>