Nth Unique Distinct Value based on cell reference

[jayped]

Good day,

If I have in cell A2 a reference and I want to return the 1st unique distinct date, the 2nd unique distinct date, the third and the fourth (aka the nth) for that reference is there an excel formula that can accomplish this? The dates will be coming from another tab which contains a list of different references. One reference will have many rows of data, and on one particular date there are many rows where the reference occurs.

Eg:

Reference
543210 11-18-2019 (1st distinct date)
543210 11-25-2019 (2nd distinct date)

I have tried this formula INDEX(Sheet1!D:D,MATCH(0,COUNTIF($C$1:C2,Sheet1!D:D),0)) but the issue is that a different reference will fall under the first so that all the references on a particular day are grouped

543210 11-18-2019 (1st distinct)
987654 11-18-2019 (1st distinct)
012345 11-18-2019 (1st distinct)

 

[JustynaMK]

Is your whole reference in the same cell -
"543210 11-18-2019" in cell A1

...or is it split between two cells? e.g.
"543210" in cell A1, "11-18-2019" in cell B2

 

[jayped]

Its split
So the reference contained in cell A2 and the date returned by a formula in B2

 

[jasonb75]

Try something like

=IF(B1="","",IFERROR(AGGREGATE(15,6,ROW(Sheet1!$D$2:$D$200)/(Sheet1!$C$2:$C$200=$A$2)/ISNA(MATCH(Sheet1!$D$2:$D$200,B$1:B1,0)),ROWS(B$2:B2)),""))

Where the formula is going into B2, the criteria reference is in A2, the dates in Sheet1 are in D2:D200 and the references in C2:C200.
Use realistically sized ranges, not full columns (dynamic named ranges will be more efficient).