Using SMALL + COLLECT to rank items by due date — duplicates showing the same row

Question

I'm building a "Top 4 Critical/High Priority" tracker on a dashboard sheet that pulls overdue items from a RAID Log using INDEX/MATCH/SMALL/COLLECT. Here's my current formula:

=IFERROR(INDEX({Task Title Ref}, MATCH(SMALL(COLLECT({Due Date ref}, {Due Date ref}, <=TODAY(), {Priority Ref}, OR(@cell  = "2-High", @cell  = "1-Critical")), Rank@row ), {Due Date ref}, 0)), "")

This works fine when all due dates are unique — but when two items share the same due date, both Rank rows return the same task title because MATCH always finds the first occurrence.

**What I've tried:**

1. Creating a helper column with `=[Due Date]@row + ([Row ID]@row / 1000000)` to make dates unique — getting `#INVALID COLUMN VALUE` regardless of whether the column type is Date or Text/Number

2. Using RAID ID (text, e.g. "RAID-002") inside SMALL — getting `#INVALID VALUE` because SMALL doesn't work on text

3. Using Row ID inside SMALL/COLLECT to bypass date ranking entirely — this seems promising but wanted to check if there's a cleaner approach

Thanks in advance!

Shaz · Answer

@sweta22 use this formula in helper column

=([Due Date]@row- DATE(2026, 1,1))+([Row ID]@row/10000)

Using this score you can run your small+collect logic without tie breaker. If you have tickets from last year , you can use any previous year like 2020 or 2000 for safety

Paul Newcome · Answer

How would you want it to populate in this case? Would you want the "ties" to be in the same cell maybe separated by a double line break such as

"First issue

Second issue"

Or would you prefer that they are in two separate cells? The challenge here would be… What if you have more than 4 that are all the same priority and all on the same date?

sweta22 · Answer

What the source data looks like (RAID Log Report):

RAID-007 and RAID-021 both have the same due date (02/06/26) but are completely different tasks. Both meet the filter criteria (1-Critical / 2-High, overdue).

What the formula is currently returning (High/Critical Item sheet):

Both Rank 1 and Rank 2 are returning RAID-007's task title — RAID-021 is being skipped entirely. This confirms the MATCH always returns the first occurrence when two rows share the same due date. Using (=IFERROR(INDEX({Task Title Ref}, MATCH(SMALL(COLLECT({Due Date ref}, {Due Date ref}, <=TODAY(), {Priority Ref}, OR(@cell  = "2-High", @cell  = "1-Critical")), Rank@row  ), {Due Date ref}, 0)), "")