Probelm with IF(Contains formula
So I have a sheet where different fellows enter their budget through a form.
I am trying to copy the budget from a certain fellow in the T&E Descritionary column on the basis of the submitter's BIN Number.
The formula I am using is
=IF(CONTAINS("507777", [Employee BIN]@row), [Fellow 2 $ Share]@row, IF(CONTAINS("507753", [Employee BIN]@row), [Fellow 2 $ Share]@row, [Fellow 3 $ Share]@row))
So when the BIN number is 507777 or 507753 it should copy the Fellow 2 $ Share
But it is copying Fellow 3 $ share regardless.
When I tried changing the formula based on the Employer Name and not BIN it's working fine.
=IF(CONTAINS("Jung Seo", [Employee Name]@row), [Fellow 2 $ Share]@row, IF(CONTAINS("Shaliyah Sledge", [Employee Name]@row), [Fellow 2 $ Share]@row, [Fellow 3 $ Share]@row))
But I want to use something other than the Name as there can be consistency issues.
I feel that it has to do something with "Numbers vs Digits"
What am I doing wrong here?
Any help is appreciated.
Thanks
Answers
-
Are the numbers genertaed by a formula? Are they copied from a different sheet? You can try adding ' before the number, I find that it helps sometimes.
You can also shorten you formula by using the AND function but this is not necessary.
Itai Perez
Reporting and Project Manager
If you found my comment helpful any reaction, Insightful, Awsome etc... would be appreciated🙂
https://www.linkedin.com/in/itai-perez/
-
Hello @Itai,
Yes, the numbers are being copied from different sheets using automation.
I will try adding ' and see if it works.
Do you have any other solution in mind?
Thanks,
Vishal Pandey -
Hi,
I hope you're well and safe!
Try something like this.=IF(CONTAINS(507777, [Employee BIN]@row), [Fellow 2 $ Share]@row, IF(CONTAINS(507753, [Employee BIN]@row), [Fellow 2 $ Share]@row, [Fellow 3 $ Share]@row))
Did that work/help?
I hope that helps!
Be safe, and have a fantastic day!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅ Did my comment(s) help/answer your question or solve your problem? Please support the Community and me by marking it - Insightful 💡- Vote Up ⬆️ - Aweseome ❤️ - or/and as the accepted answer. It will make it easier for others to find a solution or help to answer! I appreciate it, thanks!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.
-
Hello,
It still returns me the value of Fellow 3, which should not happen as the BIN is one of the above-mentioned.
I am expecting the value of Fellow 2.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.1K Get Help
- 414 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!