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

pe22 · 06/05/19

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 · 06/05/19

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

Stria LLC Logo

pe22 · 06/05/19

Thank you, Nick. Works great!!

Nick Burrus · 06/06/19

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 · 08/12/19

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å · 08/12/19

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 · 08/12/19

YES! Thank you for your quick response

Andrée Starå · 08/12/19

Excellent!

Happy to help!

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

Comments

Categories