Google Sheets Conditional Formatting custom formula not working to same as in cells

Question

So have an ArrarFormula in H5 that works exactly the way I want. It searched all the cells in column C and compares them to column A and returns TRUE when column C contains column A. The problem is that I want to move that formula to conditional formating. When I do that it only captures some of the cells, highlighted in Blue. Here is the formula.

=ARRAYFORMULA(IFNA( LEN(REGEXEXTRACT(C5:C, JOIN("|",QUERY(A5:A, "Select A where not A is null")))) > 0))

I have tried copy/pasting to conditional formatting and removing the ArrayFormula and the IFNA. I still get the same results. I know that I can just reference column H in conditional formatting, but I want to try to keep this as clean as possible.

Here is a link to the sheet. https://docs.google.com/spreadsheets/d/1xT_U_UZ27X724VAGwUjZ_6fHkE6IaegnbzQTC_kOfEg/edit?usp=sharing

Answer 1

I just modified your original formula so it can work with conditional formatting:

=IFNA( LEN(REGEXEXTRACT(C5, JOIN("|",QUERY($A$5:$A, "Select A where not A is null")))) > 0)

• Remove array formula and just use C5, the conditional formatting will automatically adjust its row based on your selected Apply to range
• You need to fix the range in your query() by locking its row and column using $

Output: