Welcome!

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?

Find more posts tagged with

Comments

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

Stria LLC Logo

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!

Sign in to join the conversation!

Quick Links

Trending discussions

Is it possible to edit an email list in an automated monthly report?
I have a monthly report I send out that has a long list of recipients. I know I can edit the recurrence of it being sent, but does anyone know of an easy way to just add or take out people that get my email without having to recreate the whole thing?
User Friendly Search
We would like to allow conference attendees to check themselves in using tablets at the conference center. I know there is a "find in sheet" magnifying glass in table view, but it is not super obvious, especially for non-Smartsheet users. Is there a better way for them to easily find their row among all the attendees so…
2 part automation delay question?
Greetings- I have a automation setup when I select the "status" column drop-down to "completed" I want the row info to be emailed to the contact to submitted it with a follow up then I want it to archive the row (aka move to another sheet). When I run the automation only 1 of the 2 requests happen. The archive happens…