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

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
Answers

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.

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

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.

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!
Help Article Resources
Categories
Check out the Formula Handbook template!