Index Match with duplicate values in results

[JoelleM92]

I was able to get some help on this last week, and I am now using the Index/Match function to create a team calendar. I'm going to be using conditional formatting to highlight each person's submitted vacation day and their scheduled day(s) off. The problem is that the formats I'm using won't recognize multiple days for each person. If I put in two days of vacation, it only highlights the first. I have one person that has two scheduled days off, but it only recognizes and highlights her first one. I'm trying to get the formats to work in the cells before adding them to the conditional formatting.

The codes in January are for matching the days off from the Dates tab for time off. It reads the first time a name is there, but not any others.

The codes in February are for matching scheduled days off found in columns AN and AO. GG has both Monday and Thursday off, but it is only reading the Monday off.

I've tried researching, and I think I need to use an array, but I can't figure it out. Attached is the workbook I have so far (with no identifying data). Thanks for any help.

[AliGW]

Try this instead:

=COUNTIFS(Table1[Type],"VAC",Table1[Name],$C9,Table1[Date],D$8)+COUNTIFS($AN$8:$AN$16,$C9,$AO$8:$AO$16,D$7)>0

[JoelleM92]

This works much better. I want different formatting for the different options (scheduled days off vs. VAC vs. PH, etc), so I broke it apart and did two different formulas. It's doing exactly what I need. Thank you so much!!

[AliGW]

Glad to have helped.

If you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.