Please could someone kindly help fix IF/INDEX/MATCH formula

Hello

I need a little help if possible

Original Formula:

=INDEX({Jira MMP's Validation Results - Jira Status}, MATCH([Req ID]@row, {Jira MMP's Validation Results - MMP ID}, 0))

Which worked perfectly to change the status in sheet 2 to match sheet 1 if REQ ID and MMP ID matched, but this is when there was only a single set of results in the sheet

I now need to adapt the formula to include the project epic so if the value of the epic = "1330" for instance update the cell

Formula:

=IF({Jira MMP's Validation Results - Epic} = "GBT1330", (INDEX({Jira MMP's Validation Results - Jira Status}, MATCH([Req ID]@row, {Jira MMP's Validation Results - MMP ID}, 0))))

Any help would be greatly appreciated.

Sheet 1

Sheet 2 to be updated

Thanks

Garry

Find more posts tagged with

Formulas

IF(

Accepted answers

All comments

Paul Newcome

It sounds like you will need an INDEX/COLLECT instead.

=INDEX(COLLECT({Range To Pull}, {1st Criteria Range}, 1st criteria, {2nd Criteria Range}, 2nd criteria), 1)

Garry McEwen

@Paul Newcome

=INDEX(COLLECT({Jira MMP's Validation Results - Jira Status}, ([Req ID]@row, {Jira MMP's Validation Results - MMP ID}, {Jira MMP's Validation Results - Epic} = "GBT1330"), 1)

Still get Unparseable..

Paul Newcome

You have an extra opening parenthesis that needs removed (after the first {Range}), and your syntax is off.

It should be

range, range, criteria, range, criteria

you have

range, criteria, range, range criteria

(note that you should also have commas between each piece)

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.