1

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?

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
EYM
  • 11
  • 1

1 Answers1

2

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))

enter image description here

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Of course I threw together an INDEX/AGGREGATE approach to column G w/o even thinking about FILTER, lol. – BigBen Jun 10 '20 at 22:32
  • I have had to change my thinking. I first go through all the new formula before falling back on the oldies. – Scott Craner Jun 10 '20 at 22:33
  • ^ IMO, this is how Excel answers should now be on SO... propose the new formulas (as I see you've been doing). – BigBen Jun 10 '20 at 22:34
  • Wow, It worked as expected Thank you!. I did not even think about using FILTER. – EYM Jun 11 '20 at 20:16