IF / CONTAINS using a range

Danielle Arteaga

Have an issue I thought I had resolved, but ...

I have a sheet that has two relevant data ranges:

Range 1 = a single column that specifies a Content Type for each row item. There are 7 possible Content Types: Data Sheet, Ordering Guide, BDM, TDM, Demo, Learning Map, Proposal

Range 2 = 15 columns that contain a Product Family for each row. Some rows have multiple Product Families, and each Product Family is contained in its own cell (thus why the range spans 15 columns)

I have a separate sheet that tracks whether I have one of each Content Type for every Product ID. To assess, I had been using this formula:

=IF(AND(CONTAINS([Product Family]@row, {Range 2}), CONTAINS("Data Sheets", {Range 1})), "True", "False")

I realize now, though, that it isn't quite doing what I want, because if the formula finds both the Product Family in Range 1 and the Content Type in Range 2, it is coming back "TRUE" even if the Content Type isn't appearing on the same row as the Product Family.

Any suggestions on how I might modify this? I tried this:

=IF(AND(CONTAINS([Product Family]@row, {Range 2}), {Range 1}="Data Sheets")), "True", "False"), but I get an INVALID OPERATION error.

Danielle Arteaga

Figured it out using a formula @Paul Newcome gave me before:

=IF(COUNTIFS({range 1}, "content type name", {range 2}, CONTAINS([Product Family]@row, @cell))>0, "Green", "Red")

But, Range 2 had to be converted from 15 separate columns into a single column using JOIN to concatenate the entries.

Thanks again, Paul!

Danielle Arteaga

Figured it out using a formula @Paul Newcome gave me before:

=IF(COUNTIFS({range 1}, "content type name", {range 2}, CONTAINS([Product Family]@row, @cell))>0, "Green", "Red")

But, Range 2 had to be converted from 15 separate columns into a single column using JOIN to concatenate the entries.

Thanks again, Paul!

Paul Newcome

Haha. Happy to help! 👍️

And yes. The ranges need to match in size so using a helper column to join the data and then referencing the helper column is definitely the way to go. Well done.

Please don't forget to mark your answer as "helpful". This will flag it as the "Best Answer" and will mark your post as "Answered" so that others searching for a similar solution can know that one may be found here.