How Can We Get All Data Referred to INDEX - AGGREGATE?

[soleimani1967]

Hi everybody,

I have on column "A", "ISSUE DATE" and on column "B", "DUE DATE" and on column "C", the selected ISSUE DATE.
I am willing to have all data matched to the selected ISSUE DATE as the results of DUE DATE on column "D".
(Please see the attached excel file)
I tried to use below formula on column "E". But I can not get all matched data.

=INDEX($B$2:$B$60,AGGREGATE(15,6,(ROW($B$2:$B$60)-1)/($A$2:$A$60=$C2),F2))

How can I correct my formula? Is there any another way?

Thanks in advance for your help.

[menem]

May I ask , why result of D7 should be 26/11/2021 ?
Note : my local date format is dd/mm/yyyy.

Regards.

[soleimani1967]

Does not take any different about format of dates. You can change the format of all columns.

[XLent]

If you're happy to just return the dates in date order then, assuming XL2013, you can use:

Please Login or Register  to view this content.

[menem]

Please try this formula in H2 and recheck the result.

=IFERROR(INDEX($B$2:$B$60,AGGREGATE(15,6,ROW($A$2:$A$60)/(COUNTIF($C$2:$C$60,$A$2:$A$60)>0),ROWS(H$1:H1))-ROW($A$1)),"")

Regards.

[soleimani1967]

Thank you for your help. Your formula as well as works.

[soleimani1967]

Please try this formula in H2 and recheck the result.

=IFERROR(INDEX($B$2:$B$60,AGGREGATE(15,6,ROW($A$2:$A$60)/(COUNTIF($C$2:$C$60,$A$2:$A$60)>0),ROWS(H$1:H1))-ROW($A$1)),"")

Regards.

Thanks. So,your formula as well as works.

[menem]

In case you need to list as 'selected issue date'.
I need to use helper columns.

By insert 2 column between C and D.
D2
=E1+1

E2
=COUNTIF(A:A,C2)+E1

F2
=IF(ROWS(F$1:F1) < =MAX(E:E),INDEX(B:B,AGGREGATE(15,6,ROW(A:A)/(A:A=LOOKUP(ROWS(F$1:F1),D:D,C:C)),
ROWS(F$1:F1)-(LOOKUP(ROWS(F$1:F1),D:D,D:D)-1))),"")

Regards.

Note : please change 'all column' reference if you got performance problems (but must start from row 1).

[Czeslaw]

The formula is a little shorter

Please Login or Register  to view this content.

[soleimani1967]

@menem

Thank you for your answer. It is a nice solution.

[soleimani1967]

The formula is a little shorter

Please Login or Register  to view this content.

Thanks. Your answer is something like the first solution sent by menem.