Sep 19 2022 07:10 PM - edited Sep 19 2022 07:14 PM
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.
Sep 19 2022 08:49 PM
Solution@DashTheBomber Hi Dash,
There were four issues that I could see:
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
Sep 19 2022 09:05 PM
Sep 19 2022 09:18 PM
@DashTheBomber My pleasure. If you wouldn't mind marking my answer as the solution it would be much appreciated.
Glad I could help.
Dexter
Sep 19 2022 08:49 PM
Solution@DashTheBomber Hi Dash,
There were four issues that I could see:
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