INDEX, MATCH and COUNTIF function are not working

hi there,

I have a trouble on the below formula. The same formula works well on Excel, Col F could return the non duplicate items. But on smartsheet, only first item (i.e. a) could return. Could someone help me? Thank You

=INDEX($A$2:$A$20,MATCH(0,COUNTIF($F$1:F1,$A$2:$A$20),0 ))

Screenshot 2020-08-17 at 10.39.58 PM.png

Screenshot 2020-08-17 at 10.35.37 PM.png

Find more posts tagged with

Accepted answers

L_123

I take that back. I forgot they added the distinct() formula.

=index(A1:A20,1)

would return the first value

=iferror(index(distinct(A1:A20),1+count(F$1:F1)),"")

will be a drag down from the second cell down to get the rest of your values

All comments

L_123

smartsheet doesn't support matrix equations. You need a helper column with a countif to get this functionality.

*it might be possible to do it with a stacked collect formula, but it would be fairly complicated.

L_123

I take that back. I forgot they added the distinct() formula.

=index(A1:A20,1)

would return the first value

=iferror(index(distinct(A1:A20),1+count(F$1:F1)),"")

will be a drag down from the second cell down to get the rest of your values

L_123

Man, I am not doing hot today. It wouldn't even take a stacked collect to do this without the distinct formula. You could use something like

=IFERROR(INDEX(COLLECT(a:a, a:a, FIND(@cell, JOIN(f$1:f1, "*")) = 0), 1 + COUNT(f$1:f1)), "")

*I would use the formula I posted originally, I just made the above to see if I could do it.

Momo Mo

Hi L@123,

It works well. Thank you very much

I tried both formula but only the first one works as expected.

The second formula will skip some of the items on Col A in return.

Anyway, thanks again. Good day!

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.