Using Array Formula To Match a Date Attributed to Specific Criteria Completion

[DashTheBomber]

Hi everyone,

I need some help with an array formula. I have attached the spreadsheet below to see if anyone can figure it

I have an array that matches a name to a specific criteria that it looks for in a different cell and sheet. For example:

In one sheet I have the date I completed my periodic physical at work. My roster sheet uses an array formula to match my name and identify whether I completed it or not within the physicals sheet. i.e.

 Pablo Escolar in the roster completed his physical and it has a 100 for it's completion signifying it's complete. In the array on the ROSTER it will then show him as an X. This is working perfectly as intended.

However, now I need a second cell with a similar formula that instead of marking as X for completed, shows the date Pablo Escolar actually completed his physical. I don't know how to change the formula from the first cell to make it show dates instead and if I can figure it out then I'll have managed to complete what my supervisor wants. I just can't figure out where the change is needed.

The formula looks like this:

=IFERROR(IF(LEFT(INDEX(Physicals!$M$2:$AB$4000,MATCH(1,(Physicals!$W$2:$W$4000=$B9)*(Physicals!$M$2:$M$4000=I$6),0),3),7)="ACTIVE ","X",INDEX(Physicals!$M$2:$AB$4000,MATCH(1,(Physicals!$W$2:$W$4000=$B9)*(Physicals!$M$2:$M$4000=I$6),0),15)),"")

I want to reiterate that I don't know where to change it, but I am aware the issue is in the bolded area somewhere, at least that's what I suspect, but I'm still learning the program, and I'm mostly self-taught. 

[#2 · Accepted solution]

@DashTheBomber Hi Dash,

There were four issues that I could see:

1. Cell J6 contained a value ("License...")which was not in the lookup range.  I copied from cell I6 to ensure a match was found
2.  The first IF statement always returned TRUE, so the second INDEX function would never evaluate.  I added an additional criteria wrapped in an AND statement to check for the header (J8) = "PERIODIC..." which for the date column returns false and the second INDEX is then evaluated.  
3. The Range used in the second INDEX function was Physicals!$M$2:$AA$4000, which did not include the Date Completed Column (AB).   
4. The column to return was 15, although I believe it should be 16.   Perhaps a column was inserted in the lookup range but the formula never updated to account for this.  

The end result which returned the date completed is as follows.  

=IFERROR(IF(AND(LEFT(INDEX(Physicals!$M$2:$AA$4000,MATCH(1,(Physicals!$W$2:$W$4000=$B9)*(Physicals!$M$2:$M$4000=J$6),0),3),7)="ACTIVE ",J$8="PERIODIC PHYSICAL FOR HEALTH"),"X",INDEX(Physicals!$M$2:$AB$4000,MATCH(1,(Physicals!$W$2:$W$4000=$B9)*(Physicals!$M$2:$M$4000=J$6),0),16)),"")

My intent was to keep your formula as close to what was provided, as possible, but there are several new Dynamic Array Formulas that are much simpler than this one, however, they require Excel 365.  If you have that version let me know and I can show you.  

Hope this helps. 

Dexter

[#3]

Oh my gods, thank you! This was it. I appreciate it so much and for the explanation too. I was working off an old formula, but couldn't quite grasp what areas needed to change. But, you cleared it up thank you.

[#4]

@DashTheBomber My pleasure.  If you wouldn't mind marking my answer as the solution it would be much appreciated.  

Glad I could help.  

Dexter