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


  • L_123
    L_123 ✭✭✭✭✭✭
    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.

  • L_123
    L_123 ✭✭✭✭✭✭
    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!