Issue with nested IF formula looking at the 1st column THEN 2nd column (to feed INDEX/MATCH data)
I am having an issue with this formula and need help :)
I am wanting the formula to look at the "Lead CTC Region" column for Canada first (and pull in the "Lead AE" data with the INDEX/MATCH piece) THEN I want the formula to look at the "Lead CTC Global Region" column (and pull in the "Lead AE" data with the INDEX/MATCH piece).
Formula: =IF([Lead CTC Region]@row = "Canada", INDEX({MSTRACCTSleadaecanada}, MATCH([Customer Name]@row, {MSTRACCTScustname}), 0), IF([Lead CTC Global Region]@row = "North America", INDEX({MSTRACCTleadaeamer}, MATCH([Customer Name]@row, {MSTRACCTScustname}), 0), IF([Lead CTC Global Region]@row = "APAC", INDEX({MSTRACCTSleadaeapac}, MATCH([Customer Name]@row, {MSTRACCTScustname}), 0), IF([Lead CTC]@row = "EMEA", INDEX({MSTRACCTSleadaeemea}, MATCH([Customer Name]@row, {MSTRACCTScustname}), 0), IF([Lead CTC Global Region]@row = "Latin America", INDEX({MSTRACCTSleadaelatam}, MATCH([Customer Name]@row, {MSTRACCTScustname}), 0))))))
It works with the "Lead CTC Global Region" column with all the different scenarios (North America, APAC, EMEA, and Latin America) but when the "Lead CTC Region" is Canada it give the #INVALID VALUE error for some reason.
The "Lead AE" column is a Contact column with multi-select. The data coming in is a contact and doesn't have any formatting issue.
Any help will be greatly appreciated!
Best Answer
-
Try adjusting the syntax of your INDEX/MATCH. The very end of each one currently looks like this:
{Range}), 0)
Try adjusting it to look like this instead:
{Range}, 0))
Answers
-
Try adjusting the syntax of your INDEX/MATCH. The very end of each one currently looks like this:
{Range}), 0)
Try adjusting it to look like this instead:
{Range}, 0))
-
Thank you that worked! :)
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!