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

Garry McEwen

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

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)