I can't apply conditional formatting on a #No Match cell
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?
Comments
-
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
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
-
Thank you, Nick. Works great!!
-
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
Dr. St Nicholas Burrus DHA, PMP
I build Smartsheets for the US Government, State Government, and about a dozen of the US Fortune 100s.
-
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))
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
YES! Thank you for your quick response
-
Excellent!
Happy to help!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives