Conditional Formatting with #No Match

Nicole Rivera

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?

Ramzi K

@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.

Cheers,

Ramzi

Ramzi K

@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.

Cheers,

Ramzi

Nicole Rivera

Thanks, Ramzi. That's exactly what I was looking for!

Jake Gustafson

Thanks, this was just what I needed today for a quick formula fix.

kimberly.gardner64071

This was so helpful!