LOOKUP() multiple criteria, return most recent value

[Dauch]

[TABLE="width: 305"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]NAME[/TD]
[TD]DOI[/TD]
[TD]Dept[/TD]
[TD]OSHA[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]3/8/2019[/TD]
[TD]Grocery[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Isa[/TD]
[TD]3/19/2019[/TD]
[TD]Perishable[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Dave[/TD]
[TD]3/24/2019[/TD]
[TD]Grocery[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Phil[/TD]
[TD]3/25/2019[/TD]
[TD]Perishable[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]Eric[/TD]
[TD]3/26/2019[/TD]
[TD]Perishable[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Anne[/TD]
[TD]3/28/2019[/TD]
[TD]Grocery[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]4/1/2019[/TD]
[TD]Perishable[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]


So, I've been scouring the boards for quite some time and I can't seem to process how to get this formula to work. I have my sheet set up so I can find the last injury (DOI) for each Department (Dept). I cannot manage to incorporate a second criteria that will limit the results to (OSHA = Yes), however.

What I am trying to accomplish is to look (in a much, much bigger file) at the dates of injuries and return the last injury for each Department, only when it is an OSHA incident. The formula that worked to get all but the OSHA perspective is:

=LOOKUP(2,1/('[Book1]Injuries '!$D:$D="Grocery"),'[Book1]Injuries '!$B:$B)

Some sensitive info within the workbooks, so I apologize for some of the strange references, but assume Book1 is the workbook and the sheet name Injuries, accordingly.

 

[James006]

Hello,

For a large database, you can make your life easier ... by inserting a Pivot Table ...

Hope this will help

 

[Dauch]

Sorry, I should have clarified that a bit. I'm trying to get the formula set up so that it can be copied into a powerpoint that runs in the facility -- basically trying to give a nice graphic of how long it has been since we have had an incident/injury. That part is good to go, just can't make it also look at the OSHA column...

 

[DanteAmor]

Try this:

=LOOKUP(2,1/(('[Book1]Injuries '!$C:$C="Grocery")*('[Book1]Injuries '!$D:$D="Yes")),'[Book1]Injuries '!$B:$B)

 

[GR00007]

You could also try this:

=MAXIFS([COLOR=#008000][FONT=Verdana]'[Book1]Injuries'[/FONT][/COLOR]!B:B,[COLOR=#008000][FONT=Verdana]'[Book1]Injuries'[/FONT][/COLOR]!C:C,"Grocery",[COLOR=#008000][FONT=Verdana]'[Book1]Injuries'[/FONT][/COLOR]!D:D,"Yes")