Matching Multiple Criteria to Extract a Date

[iLikeTigers1]

I am trying to get the extract date to be put under the "Last Use of Body Scanner" in the last image.
But before that, I need to match criteria, such as name match, most recent deployment with that equipment in use, and if that personnel is certified in that equipment before the date is confirmed.
Images are in order, including airport equipment, equipment certification, deployment, and certification tracker.

Your help is appreciated!

 

[iLikeTigers1]

I am trying to get the extract date to be put under the "Last Use of Body Scanner" in the last image.
But before that, I need to match criteria, such as name match, most recent deployment with that equipment in use, and if that personnel is certified in that equipment before the date is confirmed.
Images are in order, including airport equipment, equipment certification, deployment, and certification tracker.

Your help is appreciated!

To clarify, I need a formula that calculates those pieces of information.

 

[hagia_sofia]

Hello, could please explain in more detail what is the relationship between those screenshots? E.g. it might be possible to work with 3rd and 4th but there seems to be no connection with 1st and 2nd. Also, how the condition if "personnel is certified" is verified?

 

[iLikeTigers1]

Hello, could please explain in more detail what is the relationship between those screenshots? E.g. it might be possible to work with 3rd and 4th but there seems to be no connection with 1st and 2nd. Also, how the condition if "personnel is certified" is verifie

Hello, could please explain in more detail what is the relationship between those screenshots? E.g. it might be possible to work with 3rd and 4th but there seems to be no connection with 1st and 2nd. Also, how the condition if "personnel is certified" is verified?

Yes of course, the first is the list if that airport has the equipment. If the officer is sent to different locations, that table would need to be referenced. The cert list, states if that officer is certified for that equipment, which would need to be referenced when needing to extract the date. If they weren’t certified, then it would be n/a.

 

[hagia_sofia]

Many thanks for your reply. Based on your description, please test the following (you will need to adjust references based on your data - the references as used within the formula visible in the attachment - and adjust the date column which you are interested in, i.e. either "Insert Date" or "Extract Date" as I am not sure about that):

=IFERROR(IF(XLOOKUP(Q2&R2,$D$2:$D$20&$E$2:$E$20,$F$2:$F$20,"No")="Yes",TAKE(SORT(FILTER($M$2:$M$20,($I$2:$I$20=Q2)*($J$2:$J$20=R2)*(XLOOKUP($K$2:$K$20,$A$2:$A$20,$B$2:$B$20,"No")="Yes"))),-1),"-"),"-")