Please could someone kindly help me with this INDEX/MATCH/IF /AND Formula

Garry McEwen

Hello There Smartsheet Community

Please could you kindly point me in the right direction with my formula?

Sheet 1 Contains the following sheet references

"Status" = Jira Status

"MMP" = MMP

I need to update the value of "Validation NL" based upon the following criteria:

If "MMP" in sheet 1 matches "Req ID" in sheet 2 and the value of "Status" in sheet 1 is X update the value in "Validation NL" to X

Sheet 2

Req ID

Validation NL

Current formula :

=INDEX({MMP}, MATCH([Req ID]@row, IF({Status} =Pass, "Pass Validation", IF(AND({Status}=To-Do, "Pending", IF(AND({Status}=Fail/Issue, "Fail/Issue", IF(AND({Status}=Gap, "Gap", IF(AND({Status}=Work in progress, "Work In Progress", IF(AND({Status}=NA, "NA", IF(AND({Status}=Pass - Now Fail, "Pass - Now Fail", IF(AND({Status}=Closed, "Closed"))

I am trying to keep the two sheets separate rathe than combine into 1

Currently getting unparseable any assistance would be greatly appreciated.

Thanks

Garry

jmyzk_cloudsmart_jp

Hi @Garry McEwen

If "MMP" in sheet 1 matches "Req ID" in sheet 2 and the value of "Status" in sheet 1 is X update the value in "Validation NL" to X

[Validation NL] =JOIN(COLLECT({Sheet 1: Jira Status}, {Sheet 1: MMP}, [Req ID]@row))

In the formula above, {Sheet 1: MMP}, [Req ID]@row range & condition takes care of the "If "MMP" in sheet 1 matches "Req ID"

https://app.smartsheet.com/b/publish?EQBCT=298a843cf2b048e3be16506449cca8a5

https://app.smartsheet.com/b/publish?EQBCT=a350d467de0048bb98500fc2b15c3d2f