Conditional IF INDEX MATCH formula

I am trying to make a conditional index match statement where if "Topic A" is selected from a dropdown column called "Tags", the "Technical Lead" column will have Name A populated in it. Otherwise, it will be populated based on this index match:

=INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0))

Looking at some other discussion topics, it looks like I need to follow this pattern, but I don't understand what to put for the first index match (the value_if_true) since I only want to match it to one person's name. How can I complete the middle part of this formula?

=IF( logical_expression , value_if_true , [ value_if_false ])

=IF([Tags]@row = "Topic A", first_sheet_index_match, second_sheet_index_match)

=IF([Tags]@row = "Topic A", first_sheet_index_match, =INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0))

Thank you for any help!

Find more posts tagged with

Accepted answers

Emilio Wright

@LizTo

I was under the assumption that you needed two INDEX(MATCH())s. If that is the case and the Person A lives on the current sheet then it would be

=IF([Tags]@row = "Topic A", PersonA@row, INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0)))

Did I understand that correctly? If not, where would you be getting the value for Person A? Could you name the columns you need?

All comments

Emilio Wright

@LizTo

When setting the value_if_true and value_if_false you don't have to use the "=" sign.

This should be your formula:

=IF([Tags]@row = "Topic A", INDEX({Technical Lead}, MATCH(Region@row, {Region}, 0)), INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0)))

LizTo

Hi @Emilio Wright ,

Thank you for the response! I tried that formula and get #INVALID REF whenever "Topic A" appears in the Tags column. Any suggestions?

Emilio Wright

Invalid Ref comes up when SS can't find the reference sheet. I added a made-up reference assuming you would change it. I called it {Technical Lead}. You will have to change this reference range to the one you need to check for.

LizTo

@Emilio Wright, right, that makes sense. Do I have to put a reference, though? I only want to match it to one person's name. So if it's Topic A, Person A gets listed in that cell. If it's not Topic A, it uses the second index match where all of the other references are set up.

I guess I could set up a second reference sheet to only list Person A, but if there's a way to just capture that information in the formula, that would be simpler.

Emilio Wright

@LizTo

I was under the assumption that you needed two INDEX(MATCH())s. If that is the case and the Person A lives on the current sheet then it would be

=IF([Tags]@row = "Topic A", PersonA@row, INDEX({EMU Contact}, MATCH(Region@row, {Region}, 0)))

Did I understand that correctly? If not, where would you be getting the value for Person A? Could you name the columns you need?

LizTo

Hi @Emilio Wright,

My attempt at the formula in the original question is based on other answers I saw in the forums, like this one, which says that IF can only be used with two INDEX MATCHs, if I understood it correctly.

https://community.smartsheet.com/discussion/67935/help-with-if-index-match

However this second formula you provided works perfectly, without a first index match, so maybe I misunderstood that previous post.

Thank you very much for your patience in helping me sort this out!

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.