Is there a limit to how long your IF statement can be? If so, is there an alternative formula?

Melissa Torrez

Is there an alternative formula or solution. I need to assign a contact based on two variables a business function and a business system that are in 2 separate columns.

Paul Newcome

The limit would be 4,000 characters including spaces.

An alternative... Are you able to build out a list that could be referenced?

Melissa Torrez

I built out a list in another sheet to reference and I am struggling with the appropriate formula to use to reference the 2 variables and return a contact.

It looks like this:

for example i had this IF statement that looked at every scenario, but it got too long: =IF(AND([Finance Function]@row = "Accounts Receivable", [Finance System]@row = "MS Dynamics AX"), "Vinay Dsouza"

**Moderator removed image due to potential sensitive information.

Paul Newcome

To look at two variables, we would use an INDEX/COLLECT.

=INDEX(COLLECT({List Sheet Contact Column}, {List Sheet Function Column], Function@row, {List Sheet System Column}, System@row), 1)

Melissa Torrez

It worked. I have not used COLLECT before. Thank you!

Paul Newcome

Happy to help. 👍️