Formula Function - SMALL for dates with duplicates (ERROR)

taylorbrundage

Hi,

I'm currently using a formula to INDEX my sheet and pick up the smallest date in a column, and then display the 4 smallest (or soonest) dates in that column to pull into my dashboard. The formula is as follows:

=INDEX([Project Name]:[Status], MATCH(SMALL([Next Milestone Date]:[Next Milestone Date], 2), [Next Milestone Date]:[Next Milestone Date], 0), 1)

The "2" in the SMALL formula indicates that I want to pull the 2nd smallest value, and so on. This works great, however if there are any duplicate dates (i.e. 2 of the cells in the column are the same date), the formula will keep pulling the same value and skip any duplicates, which causes me to miss important data points. 

For example... I had 3 rows which contained the same milestone date, and I couldn't get my formula to work until I changed the SMALL value to 5, where it displayed the 5th smallest. It seems fairly random.

Is there any fix I can apply to my formula to avoid this issue? Any insight is much appreciated!

L_123

=INDEX([Project Name]:[Status], MATCH(SMALL(Distinct([Next Milestone Date]:[Next Milestone Date]), 2), [Next Milestone Date]:[Next Milestone Date], 0), 1)