Conditional Formatting with #No Match

Hi everyone, I've been working on conditional formatting utilizing the #no match on a formula (a vlookup that doesn't have a reference so we can add). I've like to have some cells highlighted (rows 1-12) when "no match" occurs, which is a possibility on the conditional formatting options, but highlights aren't happening.

Any ideas?


Best Answer

  • Ramzi K
    Ramzi K ✭✭✭✭✭
    Answer ✓

    @Nicole Rivera

    The #NO MATCH value in the cells is not a text that the conditional formatter can detect. It's actually an error that Smartsheet puts in the cell. So try using an IFERROR function in your VLOOKUP formula and forcing a text "#NO MATCH" into the cell if there is no match - like this:

    =IFERROR(VLOOKUP(LookupColumn@row, $LookupColumn$1:$[Value Column]$4, 2, false), "#NO MATCH")

    I hope this works for you.



    Ramzi Khuri - Principal Consultant @ Cedar Tree Consulting (

    Feel free to email me: [email protected]

    💡 If this post helped you out, please help the Community by marking it as the accepted answer/helpful.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!