Using IF and OR to Assign a Contact

Sam Buck

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.

Heather Duff

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 Duff

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

Sam Buck

Heather,

Thank you very much! This fixed my problem, and the explanation is extremely helpful for my thought process.