I can't apply conditional formatting on a #No Match cell

pe22

I'm trying to apply conditional formatting to a column, where I am  using vlookup.

When the vlookup doesn't find the value in the other sheet, it returns a #NO MATCH value in the cell.

This is fine, except when I try to format these #No MATCH cells, it will not take.  

I have the format setup as "If cell equals #NO MATCH (selected from a drop down list), then highlight in green".

What am I missing?

Nick Burrus

Hello,

Use IFERROR and wrap your VLOOKUP into it. Instructions here: https://help.smartsheet.com/function/iferror

This will let you change the #NO MATCH system message to something else, or even blank!

Nick

pe22

Thank you, Nick. Works great!!

Nick Burrus

Anyh time @pe22! Always ask questions here! It's how everyone learns. Smartsheet's documentation isn't 100% yet, but, these forums are an amazing tool to fill in the gap while the Smartsheet team builds more features.

I was annoyed and had headaches when I first started using Smartsheet, now it's a breeze! I use it to automate most of my job, through ways people said was impossible  

ginamt3

Hi @NBurrus. I having a similar issue. I want to display a blank if there is no match. I put an IFERROR in the front of my formula but it is not working. 

=VLOOKUP([Job Number]3, {Job-Log Range 4}, 2, false)

IFERROR (VLOOKUP([Job Number]3, {Job-Log Range 4}, 2, false))

 

Andrée Starå

Hi,

Try something like this.

=IFERROR(VLOOKUP([Job Number]3, {Job-Log Range 4}, 2, false)),"")

Did it work?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

ginamt3

YES! Thank you for your quick response 

Andrée Starå

Excellent!

Happy to help!