Same Formula returning a blank cell
Hi, I am working on updating an error message to return "No Resource Assigned" however the same formula is now returning a blank cell. Both columns have the same properties (contact list), and this same IFERROR(VLOOKUP) formula has worked on multiple rows prior to this blank cell and continues for the rest of the columns I am trying to fix.
=IFERROR(VLOOKUP(Brand@row, {Client Assignments for Affordability Progr
Range 1}, 10, false), "No Resource Assigned")
=IFERROR(VLOOKUP(Brand@row, {Client Assignments for Affordability Progr
Range 3}, 7, false), "No Resource Assigned")
Any help is greatly appreciated!
Answers
-
Do you have an entry for "No Brand" in your lookup table?
-
No Brand is not referenced in any of my other worksheets. This was added manually to the cells that were previously blank. The weird thing is that I have multiple columns with the same IFERROR(VLookup) to return No Resource Assigned if # No Match result is returned, however its not working on these columns.
-
In this screen shot you can see that it returned the result for No Brand in the Affordability BD column and also for Creon.
-
Can you provide a screenshot of the formula in the sheet itself?
-
Returning blank results
Returning Results
-
You have two different ranges which leads me to believe you are matching in two different columns on Brand@row?
-
Try Index, Match to replace the VLookup. So much better and I have had less issues like this when using the index match with the iferror. This doesn't address the specific issue you are having but I think it will help in this sheet and future formulas.
Melissa Boehl
Smartsheet Architect | TurningPoint Energy
-
Also I can copy and paste that same formula that is returning the correct result from the Affordability BD column to the Affordability Back up PGM column and its still blank
-
I have since removed the formula, saved the document and rewrote the formula and now I am returning blank cells no matter what I use.
-
Are you able to provide a screenshot of the reference table?
-
Ok. So generating a blank means you are getting a match on the brand but that the column you are pulling from is blank in the reference sheet. Double check your ranges to ensure you are definitely pulling from the correct columns. I also suggest rewriting to INDEX/MATCH for a number of reasons to include not having to worry about if the source columns get rearranged.
-
The formula is not returning any results now for any rows :O
-
It actually is returning a result. Just not the expected result. Double check your reference sheet and cross sheet references and definitely look into replacing VLOOKUP with INDEX/MATCH.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 467 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!