Conditional Formatting based on multiple values in table

[jcox1953]

I have the following table in Excel 2010:

	A	B	C
1	Name	ArriveDate	DepartDate
2	Smith	10/1/14	10/8/14
3	Black	10/22/14	10/29/14
4	Jones	11/14/14	11/21/14

I have a separate calendar template ... here is a sample from that template:

	A	B	C	D	E	F	G
1	SUN	MON	TUE	WED	THU	FRI	SAT
2	10/19/14	10/20/14	10/21/14	10/22/14	10/23/14	10/24/14	10/25/14

I would like to apply conditional formatting to the cells in the calendar template so that the background of the cell is highlighted if the date value in that cell falls within the "ArriveDate" and "DepartDate" values of a given row in the table.

In the above example, the goal would be to highlight cells D2:G2 (ideally, I'd like to give cell D2 a different highlight since it is an exact match for one of the values in "ArriveDate" or "DepartDate").

I simply don't know how to do this.

Help is greatly appreciated! Thank you in advance for your replies.

[etaf]

you should be able to use a lookup / index match

so OR( index( arrive date cell, match( calendar date , B:B, 0)) , index( depart date cell, match( calendar date , C:C, 0)) )
OR even a count()
that covers an exact match
then an AND
( index( arrive date cell, match( calendar date , B:B, 0)) > arrive date , index( depart date cell, match( calendar date , C:C, 0)) < depart date)

best if you load a example spreadsheet

[jcox1953]

you should be able to use a lookup / index match

so OR( index( arrive date cell, match( calendar date , B:B, 0)) , index( depart date cell, match( calendar date , C:C, 0)) )
OR even a count()
that covers an exact match
then an AND
( index( arrive date cell, match( calendar date , B:B, 0)) > arrive date , index( depart date cell, match( calendar date , C:C, 0)) < depart date)

best if you load a example spreadsheet

Thank you, etaf. From what you posted, I was able to figure out how to do the conditional formatting. I was able to change formatting on an exact match using a MATCH function:

=MATCH(D2,ArriveDate,0)

where I had created a named range (named "ArriveDate") for cells b1:b4. I created a separate conditional statement with a different formatting where the cell matched the DepartDate, using a similar approach.


I was able to format the range between the two dates using an AND function to combine two INDEX-MATCH functions, looking something like this (using cell E2 as the baseline):

=AND(INDEX(ArriveDate,MATCH(1,(ArriveDate<E2)*(DepartDate>E2),0)),INDEX(DepartDate,MATCH(1,(ArriveDate<E2)*(DepartDate>E2),0)))

I applied that basic formula to all the cells in the calendar table and it works great.

Thank you very much for getting me started - that was most appreciated!

[etaf]

your welcome

if my assistance has helped, and only if you wish to , there is a reputation icon * on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

[jcox1953]

Done. Like I said, you gave me enough info to figure out what I needed to do. I'm less interested in someone doing it for me and more interested in learning so that I can apply it elsewhere.

I've already gone back and added additional conditional formatting - will probably post something else on that (in a new thread) because I'm not sure if there is an easier / shorter statement that would have given me the same results.