Conditional IF INDEX MATCH formula

LizTo

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!

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?

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!