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
Check out the Formula Handbook template!