Using IF and OR to Assign a Contact
I am currently using the following:
=IF(Line@row = "Apples", {Contact 1}), IF(Line@row = "Oranges", {Contact 2}), IF(Line@row = "Pineapples", {Contact 3}), IF(OR(Line@row = "Red Grapes", Line@row = "Blue Grapes", Line@row = "Purple Grapes", Line@row = "Green Grapes"), {Contact 4}), IF(OR(Line@row = "Pears", Line@row = "Carrots"), {Contact 5}), IF(OR(Line@row = "Green Bananas", Line@row = "Yellow Bananas"), {Contact 6}), IF(Line@row = "Strawberries", {Contact 7}), IF(Line@row = "Pickles", {Contact 8}), IF(OR(Line@row = "Cucumbers", Line@row = "Broccoli"), {Contact 9}))
I want to assign the contact to a cell based off of a form entry (the fruit or vegetables). Is there something that you see that I'm doing wrong? I am referencing that contact from another sheet. My error is #UNPARSEABLE.
Food | Contact
Apples | Contact 1
Oranges | Contact 2
Pineapples | Contact 3
Red Grapes OR Blue Grapes OR Purple Grapes OR Green Grapes | Contact 4
Pears | Contact 5
...and so on.
Best Answer
-
Sam,
I'm not sure that this will fix the entire formula, but I do see an issue. Each IF statement needs to stay open if you're nesting another IF within it. Right now you have close parenthesis at the end of each contact name, which tells it to stop thinking.
=IF(Line@row = "Apples", {Contact 1}, IF(Line@row = "Oranges", {Contact 2}, IF(Line@row = "Pineapples", {Contact 3}, IF(OR(Line@row = "Red Grapes", Line@row = "Blue Grapes", Line@row = "Purple Grapes", Line@row = "Green Grapes"), {Contact 4}, IF(OR(Line@row = "Pears", Line@row = "Carrots"), {Contact 5}, IF(OR(Line@row = "Green Bananas", Line@row = "Yellow Bananas"), {Contact 6}, IF(Line@row = "Strawberries", {Contact 7}, IF(Line@row = "Pickles", {Contact 8}, IF(OR(Line@row = "Cucumbers", Line@row = "Broccoli"), {Contact 9})))))))))
Basically, you save all of the end parenthesis associated with the IF statements until the very end. You'll end up with the same number of end parenthesis at the end of the formula as the number of IF statements in your formula.
Let me know if it works!
Best,
Heather
Answers
-
Sam,
I'm not sure that this will fix the entire formula, but I do see an issue. Each IF statement needs to stay open if you're nesting another IF within it. Right now you have close parenthesis at the end of each contact name, which tells it to stop thinking.
=IF(Line@row = "Apples", {Contact 1}, IF(Line@row = "Oranges", {Contact 2}, IF(Line@row = "Pineapples", {Contact 3}, IF(OR(Line@row = "Red Grapes", Line@row = "Blue Grapes", Line@row = "Purple Grapes", Line@row = "Green Grapes"), {Contact 4}, IF(OR(Line@row = "Pears", Line@row = "Carrots"), {Contact 5}, IF(OR(Line@row = "Green Bananas", Line@row = "Yellow Bananas"), {Contact 6}, IF(Line@row = "Strawberries", {Contact 7}, IF(Line@row = "Pickles", {Contact 8}, IF(OR(Line@row = "Cucumbers", Line@row = "Broccoli"), {Contact 9})))))))))
Basically, you save all of the end parenthesis associated with the IF statements until the very end. You'll end up with the same number of end parenthesis at the end of the formula as the number of IF statements in your formula.
Let me know if it works!
Best,
Heather
-
Heather,
Thank you very much! This fixed my problem, and the explanation is extremely helpful for my thought process.
Categories
- All Categories
- 14 Welcome to the Community
- 10.8K Get Help
- 65 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.6K Ideas & Feature Requests
- 56 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives