#Invalid Operation with MATCH formula

=MATCH([Program Unique ID]@row, [Program Unique ID]:[Program Unique ID], 0)

I am trying to use this formula to return which row # the first match is in so that I can automatically mark the first one and differentiate it from following duplicates, but my formula is returning #invalid operation.

Open to ideas on how to fix this or other ways to get what I am looking for.

Find more posts tagged with

Accepted answers

All comments

BullandKhmer

Use a checkbox column insert the below formula. Should identify the first of any duplicates.

=IF(COUNTIF([Program Unique ID]$1:[Program Unique ID]@row, [Program Unique ID]@row) <> 1, 0, 1))

CAN Program Team

https://community.smartsheet.com/discussion/comment/351836#Comment_351836

Thanks BullandKmer, that makes sense to me, but it still isn't working. I tried that and also tried this:

=IF(COUNTIF([Program Unique ID]1:[Program Unique ID]@row, [Program Unique ID]@row) > 1, 0, 1)

but I can't seem to get the COUNTIF function to work even on its own. I just keep getting the same Invalid Operation Error.

Paul Newcome

Double check the column you are referencing. It sounds like that error may be present in that column which would push through to any formulas referencing it.

CAN Program Team

https://community.smartsheet.com/discussion/comment/351878#Comment_351878

There is no error in the column I'm referencing. It is a text-type column and populates using a formula. It is working as it should.

Paul Newcome

Did you double check no filters or anything are applied? The only way to really get that particular error out of a MATCH function is to have it in a referenced range or have a bug on the back-end. Your COUNTIF also does not have any syntax issues that would throw that error.

BullandKhmer

Weird... I cant help... sorry

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.