I need some help. I have a spreadsheet with 3 columns with a lot of dates of small projects:

Column A: startdates
Column B: halfway-dates
Column C: enddates

New dates are added daily, depending on status-updates of the projects. For obvious reasons column A has the most dates and column C the least.

What I want to display is the average time between the stages for the last 10 projects that have ended. It’s failry easy to list the 10 most recent enddates from column C in a helper-column with the LARGE-function. I am however not always finding the correct matching dates (same row) in columns A and B. The problem is that multiple projects often end on the same date, so when I list my top 10 enddates there are always some identical values. With my limited knowledge of functions XLOOKUP or INDEX it always returns the values from the first match, even though some of the identical enddates have different start- and halfwaydates.

My guess is I should be using another function (I’ve seen some AGGREGATE examples online) to ignore a row that has previously been returned in the top 10, but I can figure it out.

Anyone?

P.S. not important??: sometimes there are gaps in the ranges B and C: started projects arent always finished in order. I think it shouldn’t matter since the values in column C will always have matching values in columns A and B.

I tried XLOOKUP and INDEX with nested LARGE function, noob-stuff. It returns the first match for every identical input, it doesn’t ignore previously returned values (rows).

I tried an idex-aggregate example I found online:

=index('a:a, aggregate(15, 6, row($2:$999)/(C:C=LARGE(C:C,1), row(1:1)))

dragged it down for 10 rows, returned errors.

I’ll gladly make another helper column with unique ID-s (1,2,3,4…), but it will find the first ID it matches every time.