Probelm with IF(Contains formula

vishalpandey

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

Itai Perez

Hi @vishalpandey

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.

vishalpandey

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

Andrée Starå

https://community.smartsheet.com/discussion/128499/probelm-with-if-contains-formula

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!

vishalpandey

https://community.smartsheet.com/discussion/comment/446755#Comment_446755

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.