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

## Answers

• ✭✭✭✭✭✭
edited 08/17/20

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.

• ✭✭✭✭✭✭
edited 08/17/20

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!