Thread: List dates when multiple criteria is met

Hi!

So I'm having a bit of trouble listing dates when multiple criteria is met. I have attached a sample of my situation. This is just a portion as the actual data information is 10000+ rows long.

I have a hard time explaining this but basically what I want is something like this:

- Find the first date (Column D) where A="Griffin, Stewie" and g="16" in the "Paste" tab and list that date in cell G4 of the SAT tab.

Now find the next date where both those criteria are met. and list that in P4 in SAT tab.
and so on...

In the sample I manually entered is what I would like it to look like in Row 7.

I apologize if this sounds confusing, but I was having a hard time explaining this. I hope that the sample workbook makes it a lot easier to understand.

Thanks.

PSD, given use of XL2007 you might find the below is not "too" expensive performance wise

DATES

Code:

G4: =IF($A4="","",IFERROR(MIN(IF(Paste!$A$2:$A$10000=$A4,Paste!$D$2:$D$10000)),""))
confirmed with CTRL + SHIFT + ENTER

P4:
=IF(G4=0,0,IFERROR(MIN(IF((Paste!$A$2:$A$10000=$A4)*(Paste!$D$2:$D$10000>G4),Paste!$D$2:$D$10000)),""))
confirmed with CTRL + SHIFT + ENTER
copied to Y4 & AH4
apply Custom Format to G, P4, Y4 & AH4 of: mm/dd/yyyy;;

RESULTS

Code:

H4: 
=IF(G4=0,"",IFERROR(AVERAGEIFS(Paste!$J$2:$J$10000,Paste!$A$2:$A$10000,$A4,Paste!$D$2:$D$10000,G4,Paste!$G$2:$G$10000,H$1),""))

J4:
=IF(G4=0,"",IFERROR(AVERAGEIFS(Paste!$J$2:$J$10000,Paste!$A$2:$A$10000,$A4,Paste!$D$2:$D$10000,G4,Paste!$G$2:$G$10000,J$1),""))

L4:
=IF(G4=0,"",IFERROR(AVERAGEIFS(Paste!$J$2:$J$10000,Paste!$A$2:$A$10000,$A4,Paste!$D$2:$D$10000,G4,Paste!$G$2:$G$10000,L$1),""))

N4:
=IF(G4=0,"",IFERROR(AVERAGEIFS(Paste!$J$2:$J$10000,Paste!$A$2:$A$10000,$A4,Paste!$D$2:$D$10000,G4,Paste!$G$2:$G$10000,N$1),""))

H4:O4 can be copied to Q4:X4, Z4:AG4, AI4:AP4

If the above proves too slow then you should consider other approaches (specifically based upon sorted source data & "keys" etc...)