IF formula for INDEX/MATCH across sheets

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!

Find more posts tagged with

Formulas

INDEX

MATCH

Accepted answers

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)

All comments

Adam Murphy

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.