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

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