EXCEL Finding a cell text when calculating a Maximum value with INDEX and MAX

Question

I have been trying to get this to work for days but I am not getting anywhere.

I have a sheet with locations and temperature recordings during different days by different people.

I would like to find the latest date a measure was taken by location and the name of who took the recording only if he/she is a supervisor:

Locat.  Name    Title       Date        Latest measure  I Want this??   and this??
CA23    Tom     Supervisor  8/5/2018    2/24/2020       1/15/2019       Tom
CA23    Tom     Supervisor  1/15/2019   2/24/2020       1/15/2019       Tom
CA23    John    Contractor  2/24/2020   2/24/2020       1/15/2019       Tom
AZ58    Tina    Supervisor  6/25/2019   12/21/2019      6/25/2019       Tina
AZ58    Jose    Contractor  7/28/2018   12/21/2019      6/25/2019       Tina
AZ58    Karl    Contractor  12/21/2019  12/21/2019      6/25/2019       Tina
FL61    Tony    Contractor  3/26/2019   3/15/2020       3/15/2020       Linda
FL61    Emma    Supervisor  8/28/2019   3/15/2020       3/15/2020       Linda
FL61    Linda   Supervisor  3/15/2020   3/15/2020       3/15/2020       Linda

To get the latest date by location I used =MAXIFS(D3:D11,A3:A11,A3) but I have not been able to put a condition to count the date only if the title is a supervisor and even less to get the name of the supervisor who took the latest measure by location.

Can anyone point me in the right direction?

Answer 1

MAXIFS allows multiple criteria:

=MAXIFS(D:D,C:C,"Supervisor",A:A,A2)

Then if one has the Dynamic Array formula FILTER, for the name:

=@FILTER(B:B,(D:D=F2)*(A:A=A2))

If one does not have FILTER then look here for how to do INDEX with multiple criteria: Vlookup using 2 columns to reference another