#NO MATCH on MIN/COLLECT function

Jesse_I

Hi, I am setting up two very similar sheets, with the same columns and similar data (each sheet is for a different date range). In both sheets, I have a column that uses a MIN/COLLECT function to detect duplicates when new rows are moved into the sheet. The formula works in one sheet, but in the other, it returns a #NO MATCH error, and I can't see why.

The formula is:

=IF(ISBLANK(AVProjectID@row), 0, IF(MIN(COLLECT([Row ID]:[Row ID], [date.projectid]:[date.projectid], [date.projectid]@row)) = [Row ID]@row, 0, 1))

Here's the first sheet, where the formula works:

And here's the second sheet, where it doesn't:

I can't see any important differences between these two sheets that would cause the second to return an error. Any ideas what might be causing this? Thanks in advance.

Jesse_I

Hi Paul,

I figured it out! Just needed to sleep on it, I guess. A few rows at the bottom of the sheet had errors in the date.projectid column, which broke the whole collect formula. Added an IFERROR to the date.projectid column which seems to have fixed it.

I am somewhat curious about why an error in an individual cell of the criterion range will break the whole collect formula… Is there a way to instruct a collect formula to ignore errors in the criterion range?

Thanks,
Jesse

Paul Newcome

Do any of the Row IDs in the second sheet (the one not working) have any leading zeros?

Jesse_I

Hi Paul,

I figured it out! Just needed to sleep on it, I guess. A few rows at the bottom of the sheet had errors in the date.projectid column, which broke the whole collect formula. Added an IFERROR to the date.projectid column which seems to have fixed it.

I am somewhat curious about why an error in an individual cell of the criterion range will break the whole collect formula… Is there a way to instruct a collect formula to ignore errors in the criterion range?

Thanks,
Jesse