INDEX, MATCH and COUNTIF function are not working

Momo Mo

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 ))

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

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!