If/and referencing multiple sheets

MB1234

Looking to express the following:

If [ID#] in sheet A is equal to [ID#@Row] (in sheet B),

AND [Progress] in sheet A is between 90 and 100, show "Published" in [Progress] in sheet B,

If [Progress] in sheet A is between 15 and 89, show "In Progress" in [Progress] in sheet B,

If [Progress] in sheet A is between 0 and 14, show "Not Started" in [Progress] in sheet B

Have been unsuccessfully been trying with different variations of:

=IF{ID# Range1), [ID#] @Row, AND(({Progress Range 1} <100, >90), "Published"), (({Progress Range 1} <89, >15), "In Progress"), (({Progress Range 1} <100, >90), "Not Started")))

Nick Korna

Hi @MB1234,

Something like this should do the trick for you:

=IF(INDEX({Sheet A Progress}, MATCH([ID#]@row, {Sheet A ID#})) >= 90, "Published", IF(INDEX({Sheet A Progress}, MATCH([ID#]@row, {Sheet A ID#})) >= 15, "In Progress", "Not Started"))

Sample Sheet A data:

Sample Sheet B output:

Hope this helps, but if you've any problems/questions then just post! 🙂