IF(MATCH with Multiple Criteria Across Sheets

Mad Ed
Mad Ed
edited 03/14/25 in Formulas and Functions

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

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!