CODEWITHSUNDEEP
excelformula
Umar.H
Umar.H

Reputation: 23099

Index Match to return MAX Date with multiple criteria

apologies for this, I'm assuming this is simple but a few hours of SO googling hasn't helped

Enough whining from me:

Consider the following dataset:

ID, Date, Review, Review    Status
1   01/02/18, "Cool",       Positive
1   01/03/18, "Awesome", "  Positive
1   01/01/18, "Cumbersome", Negative
1   01/02/18, "Rubbish!", " Negative

I'm currently using an array type index match to get the latest review based on a few conditions

I have two columns one which says positive, one negative.

in each of the column I would like to return the latest positive review but I'm unsure how to get the max date within the below formula:

{=index(C2:C4, MATCH(1,(1 = A2:A4)*("Positive" = D2:D4)*(maxdatehere = B2:B4),0))}

The data I have is around 7k rows and is Google Review Data and pretty much matches the example above.

I'd rather not use VBA as I've never really used it before (but will do so grudgingly)

as this is excel I've not created a google demo sheet, but happy to do so for ease of the experts and for others to benfit if they find there way here one day.

Upvotes: 0

Views: 6992

Answers (1)

Scott Craner
Scott Craner

Reputation: 152475

If you have Office 365:

=INDEX(C2:C5, MATCH(1,(1 = A2:A5)*("Positive" = D2:D5)*(MAXIFS(B:B,A:A,1,D:D,"Positive") = B2:B5),0))

Confirme with Ctrl-Shift-enter instead of Enter when exiting edit mode.

enter image description here

If you have 2010 or later then:

=INDEX(C:C,AGGREGATE(15,7,ROW(C2:C5)/((A2:A5=1)*(D2:D5="Positive")*(AGGREGATE(14,7,B2:B5/((A2:A5=1)*(D2:D5="Positive")),1)=B2:B5)),1))

Entered normally.

enter image description here

Upvotes: 2

Related Questions