Can I conditionally format a cell if it contains text from a range consisting of multiple rows?

Nicole Casuso

I have a column containing text (multiselect drop down) that I would like to eventually conditionally format based on the presence of matching text from cells in a range made of multiple rows. Here is fictitious data in an excel format for simplicity.

Sheet 1 Range 1 (Column C) contains the data I want to format based on whether or not each cell contains a value found in Range 2 of a different Sheet.

Sheet 2 Range 2 (Column A) contains multiple individual values that I would like to search for in Column C.

Based on these examples, C3 and C7 would not be formatted because those cells do not contain any of the values present in Range 2.

I understand a helper column with "yes" "no" regarding the matching text is likely necessary prior to being able to conditionally format.

Is this possible? My actual data set contains over 1000 Rows to format (Range 1 Column C) and over 150 Rows to search for matching text (Range 2 Column A).

Lucas Rayala

Hi @Nicole Casuso -- this is a tricky one because you're essentially asking to search one range for another range, which Smartsheet doesn't really support. IF the number of selections in the dropdown is limited, you can create a helper column with a stacked formula, where the first part will grab the first entry in the dropdown and search for that, and the second part will grab the second entry and search for that, etc. It's super ugly, but you can get it to work.