If two criteria place in third criteria column

Hello I've attached an excel workbook with the follow worksheets:

1. Entry Page - The only data that would change every month would be columns "Day Off (Y/N)" and "Day of the Month"
2. Extended Data (Manual) - This is what I want it to ultimately look like.
3. Extended Data (Blank) - The blank portion is where I need help filling in with an automatic formulas.

What I want the formula to read: If [Group] and [Panel] have a "Yes" in the "Day Off" column then read the number in the column of "Day of the Month" and place a .75 in rows that have the [Group] column as "Group A" AND any text in the [Panel] column as "sub-group aa"

For clarity purposes, in the extended data tab you have duplicate panel names, is this correct?

Also in your entry page you have "sub-group aa" as an example but this exact match doesn't appear in the extended data tab... will the match you will be looking for on the extended data tab be only on the first portion of the panel text?

Originally Posted by Bo_Ry

Please try at D6
=1-OR(INDEX(REPT('ENTRY PAGE'!$D$2:$D$5,(('ENTRY PAGE'!$A$2:$A$5=$A6)*ISNUMBER(SEARCH('ENTRY PAGE'!$B$2:$B$5,$B6))*('ENTRY PAGE'!$C$2:$C$5="Yes")))=D$5,))/4

nice! formula I had in mind was ugly compared to this!

You are a genius! Thank you, it's exactly what I needed.

Originally Posted by Bo_Ry

Please try at D6
=1-OR(INDEX(REPT('ENTRY PAGE'!$D$2:$D$5,(('ENTRY PAGE'!$A$2:$A$5=$A6)*ISNUMBER(SEARCH('ENTRY PAGE'!$B$2:$B$5,$B6))*('ENTRY PAGE'!$C$2:$C$5="Yes")))=D$5,))/4

You are a genius! Thank you very much. It's exactly what I needed.

Originally Posted by Bo_Ry

Please try at D6
=1-OR(INDEX(REPT('ENTRY PAGE'!$D$2:$D$5,(('ENTRY PAGE'!$A$2:$A$5=$A6)*ISNUMBER(SEARCH('ENTRY PAGE'!$B$2:$B$5,$B6))*('ENTRY PAGE'!$C$2:$C$5="Yes")))=D$5,))/4

Sorry Bo_Ry, I need one more addition to the formula. I am pretty new to the forum. Should I place my question here or create a new thread?

I'd like to add almost the same portion as the panel onto the Group.

For example: If any part of [Group] says "Group A" AND any part of [Panel] says "sub-group aa" AND the [Day Off] says "Yes" then find the date in the [Day of the Month] and place 0.75, if not place a 1.

The formula Bo_Ry created was perfect, however, I just need to add the AND [Group] portion.

I've attached the version 2 workbook and highlighted what needs to be accounted for in Green. I really appreciate all the help everyone!

Hi Glenn, Since no one has answered my question should I repost? or how about do I got to uncheck this as 'solved'? Thank you

Good Morning Bo_Ry! I've need of your kind assistance once again.

The formula is working to search whether if anything in [GROUP] and anything in [PANEL] corresponds to "Yes" and the date then it would 0.75 because SOME people are present.

However, the problem is if the [GROUP] and the [PANEL] say "Yes" and the [GROUP] and the [PANEL] are the only group and panel selected it should be 0 because NOONE is present. This is represented in the yellow highlighted portion in the excel sheet v.3.

I hope you can help and I want to say thanks for helping me these past couple of months.