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!

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

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))

All comments

Paul Newcome

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))

Katlyn Gossett

Thank you that worked! :)

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.