Conditional IF INDEX MATCH formula

Options

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!

Best Answer

  • Emilio Wright
    Emilio Wright ✭✭✭✭✭
    Answer ✓
    Options

    @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?

Answers

  • Emilio Wright
    Emilio Wright ✭✭✭✭✭
    Options

    @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
    LizTo ✭✭✭✭✭
    edited 02/06/23
    Options

    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
    Emilio Wright ✭✭✭✭✭
    Options

    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
    LizTo ✭✭✭✭✭
    Options

    @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
    Emilio Wright ✭✭✭✭✭
    Answer ✓
    Options

    @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
    LizTo ✭✭✭✭✭
    Options

    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.


    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!