If/and referencing multiple sheets

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")))

Find more posts tagged with

Formulas

Accepted answers

All comments

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! 🙂

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.