IF(MATCH with Multiple Criteria Across Sheets

Mad Ed

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

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. 👍️