I need help with conditional formatting in Google Sheets. I have two worksheets with different data structures.
In the first worksheet, column B contains lists separated by commas:
B1 contains: apple, banana, orange
B2 contains: car, truck, bike
In the second worksheet, column B has single items in each row:
B1 contains: Car
B2 contains: Horse
B3 contains: Orange
I want to set up conditional formatting so that cells in the first sheet get highlighted when they contain any value that matches something from the second sheet’s range.
I tried using the text contains option with Sheet2!B:B but it didn’t work. I also tested custom formulas like SEARCH and FIND but couldn’t get them to work with ranges. I got one formula working with a single value but not with multiple values or ranges.
Maybe I need to use ARRAYFORMULA but I’m not sure how it works exactly. Should I write a script instead or is there a simpler way to do this with conditional formatting?
I hit this same issue six months ago. COUNTIF with wildcards worked way better than the other methods people suggested. I used =SUMPRODUCT(COUNTIF(B1,"*"&Sheet2!B:B&"*"))>0 for conditional formatting. It checks each item from Sheet2 against your comma-separated string in B1 using wildcards, so it catches partial matches in your text. COUNTIF handles partial matching more reliably than SEARCH or REGEX when you’re dealing with comma-separated values. Just make sure you define your Sheet2 range properly - I always use exact ranges like Sheet2!B1:B10 instead of entire columns or it’ll slow things down. Applied it to my whole Sheet1 range and it highlighted everything correctly, even handled case differences automatically.
try using =SUMPRODUCT(ISNUMBER(SEARCH(Sheet2!B:B,B1)))>0 in your conditional formatting. it checks if any items from sheet2 match the text in your comma-separated lists. worked for me in a similar setup - just change the range to fit how many rows you have in sheet2.
You need a different approach for conditional formatting with ranges. Try this formula: =REGEXMATCH(LOWER(B1),LOWER(TEXTJOIN("|",TRUE,Sheet2!B:B))). It converts everything to lowercase so case doesn’t matter, then uses TEXTJOIN to build a regex pattern that checks if any word from Sheet2 shows up in your cell. Had this exact problem last month - this formula nailed it. Apply it to your whole range, not just single cells. REGEXMATCH beats SEARCH when you’re dealing with multiple values and comma-separated stuff.