Using Multiple IF(CONTAINS with INDEX(MATCH Getting #NO MATCH error

Options
Mr. Chris
Mr. Chris ✭✭✭✭✭
edited 08/31/22 in Formulas and Functions

Hello!

I have two formulas that works great when broken in two parts, however when combined I'm getting a #NO MATCH in my cell.

Formula 1 (works):

=IF(CONTAINS("DONE Delivery of Assets", $[Status (External SS)]@row), (INDEX({Cedar Review 360 Link}, MATCH($[Course Title]@row, {Cedar Course}, 0))))

Formula 2 (works):

=IF(CONTAINS("DONE Delivery of Assets", $[Status (External SS)]@row), INDEX({WG Ansr Review 360 Link}, MATCH($[Course Title]@row, {WG Ansr Course}, 0)))

Combined (not working):

=IF(CONTAINS("DONE Delivery of Assets", $[Status (External SS)]@row), (INDEX({Cedar Review 360 Link}, MATCH($[Course Title]@row, {Cedar Course}, 0))), IF(CONTAINS("DONE Delivery of Assets", $[Status (External SS)]@row), (INDEX({WG Ansr Review 360 Link}, MATCH($[Course Title]@row, {Ansr Course}, 0))), ""))

I have spent hours on this and can't seem to get formula 2 to populate when combined with formula 1.

Any help would be greatly appreciated!

Best Answer

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Mike TV,

    I appreciate your input. I was able to finally get it work, although it doesn't look pretty, haha!

    Basically bypassed the #NO MATCH error, by beginning the formula with IFERROR.

    Once the #NO MATCH is triggered, the formula proceeds to the following INDEX MATCH.

    It looks like I have something repeated after the 2nd IF(CONTAINS section to populate the FALSE result, but it works nonetheless.

    =IFERROR(IF(CONTAINS("DONE Delivery of Assets", [Status (External SS)]@row), INDEX({Cedar Review 360 Link}, MATCH($[Course Title]@row, {Cedar Course}, 0)), IF(CONTAINS("DONE Delivery of Assets", $[Status (External SS)]@row), INDEX({WG Ansr Review 360 Link}, MATCH($[Course Title]@row, {WG Ansr Course}, 0)))), INDEX({WG Ansr Review 360 Link}, MATCH($[Course Title]@row, {WG Ansr Course}, 0)))

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @.Chris

    Try this:

    =IF(CONTAINS("DONE Delivery of Assets", $[Status (External SS)]@row), INDEX({Cedar Review 360 Link}, MATCH($[Course Title]@row, {Cedar Course}, 0)), IF(CONTAINS("DONE Delivery of Assets", $[Status (External SS)]@row), INDEX({WG Ansr Review 360 Link}, MATCH($[Course Title]@row, {Ansr Course}, 0)), "")

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @.Chris

    Upon further review of your formula, it looks like the criteria in the first IF and the nested IF are the exact same. So I don't think it's ever going to output the 2nd results because if the criteria (which are the same) is met in the first IF statement you'll get the INDEX MATCH it's set up to output.

  • Mr. Chris
    Mr. Chris ✭✭✭✭✭
    Answer ✓
    Options

    Hi @Mike TV,

    I appreciate your input. I was able to finally get it work, although it doesn't look pretty, haha!

    Basically bypassed the #NO MATCH error, by beginning the formula with IFERROR.

    Once the #NO MATCH is triggered, the formula proceeds to the following INDEX MATCH.

    It looks like I have something repeated after the 2nd IF(CONTAINS section to populate the FALSE result, but it works nonetheless.

    =IFERROR(IF(CONTAINS("DONE Delivery of Assets", [Status (External SS)]@row), INDEX({Cedar Review 360 Link}, MATCH($[Course Title]@row, {Cedar Course}, 0)), IF(CONTAINS("DONE Delivery of Assets", $[Status (External SS)]@row), INDEX({WG Ansr Review 360 Link}, MATCH($[Course Title]@row, {WG Ansr Course}, 0)))), INDEX({WG Ansr Review 360 Link}, MATCH($[Course Title]@row, {WG Ansr Course}, 0)))

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @.Chris

    One of the things I noticed you were doing that I wasn't sure why is you were wrapping your INDEX MATCH up in parenthesis. That could have been part of your problems.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!