IF formula for INDEX/MATCH across sheets

rmartinsasp

Hi, I'm really struggling with this one. I have a sheet that has a drop-down with options for "Yes" and "No" and I want to perform an INDEX/MATCH function based on another cell in that row, but only if the result of the drop-down in that row is Yes.

The sheet reference for the Yes/No drop down is {App_S}, and the two cells I want to match are {App_Ref} and [Ref]@row - and the cell I want to return is {App_JT}

I have tried variations of the following:

=IF({App_S} = "Yes", INDEX({App_JT}, MATCH([Ref #]@row, {App_Ref}, 0)))

…but I get an "INVALID OPERATION" return?

Please help!

Adam Murphy

HI @rmartinsasp, I would try something like =INDEX(COLLECT({App_JT}, {App_Ref}, [Ref]@row, {App_S}, "Yes"), 0). As column formula this should return the value in App_JT when App_ref matches Ref@row and {App_S} is "Yes". Would this work? Hope it helps!

Sandy Drew

I would recommend using an Index/Collect. This gives you the flexibility to add a number of criteria within your formula.

=INDEX(COLLECT({App_JT}, {App_S}, "Yes", {App_Ref}, [Ref #]@row), 1)

Adam Murphy

HI @rmartinsasp, I would try something like =INDEX(COLLECT({App_JT}, {App_Ref}, [Ref]@row, {App_S}, "Yes"), 0). As column formula this should return the value in App_JT when App_ref matches Ref@row and {App_S} is "Yes". Would this work? Hope it helps!

Sandy Drew

I would recommend using an Index/Collect. This gives you the flexibility to add a number of criteria within your formula.

=INDEX(COLLECT({App_JT}, {App_S}, "Yes", {App_Ref}, [Ref #]@row), 1)

rmartinsasp

Thank you Sandy and Adam - that worked perfectly! I'm so grateful!