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! :)
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.2K Get Help
- 430 Global Discussions
- 152 Industry Talk
- 490 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 75 Community Job Board
- 501 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 306 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!