Using Vlookup or Index/Match based on a Max value

[kwread]

I have a data set that has a list of interactions, including organization name, status of interaction, date of the interaction and score of the interaction. I am hoping to write a lookup that can find the score, for the most recent, completed interaction with each organization.
Example of what this looks like below.
I want to be write a lookup such that for each organization name it pulls the score for the most recent completed interaction
So for
Franks -> Should pull a 3
For Jims it would pull 4, because even though the score of 1 was more recent, that interaction is still pending

Etc etc for many installers

Not really sure how to get this to work. I can use Maxifs to return the max date value for a given installer and status, but not sure how to then translate that over to the score column?

Thanks

Organization Name	Interaction Status	Interaction Score	Interaction Date
Jims	Complete	2	1/1/2018
Franks	Complete	3	1/1/2017
Franks	Pending	3	2/1/2017
Jims	Complete	4	9/1/2019
Jims	Pending	1	10/1/2019
Jims	Complete	3	2/1/2019
Jims	Complete	4	2/1/2018

 

[Fluff]

What version of Xl are you using?

 

[kwread]

What version of Xl are you using?

Of Excel? Im on Office 365 Pro Plus - Excel version is 1908

 

[Fluff]

That sounds as though you might not have the new dynamic arrays yet, is that right?

 

[kwread]

That sounds as though you might not have the new dynamic arrays yet, is that right?

"Dynamic Arrays" isnt a term ive heard before so im not actually sure....

I think i may have found a potential solution Using a MaxIfs in one column to return the Date of the most recent inspection, and then in the next column using a Index(Match(1,(Condtion)*Condition*Condtion...) based on that date in the next.

It seems to be working, though is two big array formulas so seems calculation intensive.

If anyone has ideas for simpler solutions let me know but this does at least seem to be feasible

 

[Fluff]

If you type =seq into a cell, does it show you SEQUENCE as an option?

 

[Fluff]

Ignore that, how about

+Fluff.xlsm
	A	B	C	D	E	F	G
1	Organization Name	Interaction Status	Interaction Score	Interaction Date
2	Jims	Complete	2	01/01/2018		Jims	4
3	Franks	Complete	3	01/01/2017		Franks	3
4	Franks	Pending	3	02/01/2017
5	Jims	Complete	4	09/01/2019
6	Jims	Pending	1	10/01/2019
7	Jims	Complete	3	02/01/2019
8	Jims	Complete	4	02/01/2018
Finals

Cell Formulas
Range	Formula
G2:G3	G2	=AGGREGATE(15,6,$C$2:$C$8/($D$2:$D$8=MAXIFS($D$2:$D$8,$A$2:$A$8,F2,$B$2:$B$8,"Complete")),1)