IF(MATCH with Multiple Criteria Across Sheets

I am attempting to create a formula that will search another sheet for a product code and as long as the product code isn't listed as "Canceled" on the other sheet, return "Yes".

I started with =IF(CONTAINS([Product Code]@row, {Upcoming Products}), "YES", "NO").

When I tried to add the condition, I got a little lost on how to add both criteria. Looking through articles, this is what I ended up with =IF(ISERROR(MATCH([Product Code]@row, {Upcoming Products}, 0)), "NO", AND(IF(MATCH([Product Code]@row, {Upcoming Products}, 0), IF(NOT(INDEX(COLLECT({Status}) = "Canceled")), "NO", "YES"))))

I've attached sample data pictures to help explain

Find more posts tagged with

Formulas

IF(

MATCH

Accepted answers

All comments

Paul Newcome

Try counting how many rows have that product code and "Cancelled". If that count is greater than zero then the product code has been cancelled.

=IF(COUNTIFS({Code}, @cell = [Product Code]@row, {Status}, @cell = "Cancelled") > 0, "NO", "YES")

Mad Ed

@Paul Newcome Thank you for solving the "Canceled" portion of the formula! I had to add it into the original formula so that it didn't put "Yes" for Product Codes that weren't matched across both sheets.

=IF(NOT(CONTAINS([Product Code]@row, {Upcoming Products})), "NO", IF(COUNTIFS({Upcoming Products}, @cell = [Product Code]@row, {Status}, @cell = "Canceled") > 0, "NO", "YES"))

Paul Newcome

Happy to help. 👍️

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.