Is there a limit to how long your IF statement can be? If so, is there an alternative formula?
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.
Answers
-
The limit would be 4,000 characters including spaces.
An alternative... Are you able to build out a list that could be referenced?
-
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.
-
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)
-
It worked. I have not used COLLECT before. Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!