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

Mr. Chris

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!

Mr. Chris

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

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

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

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

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