Google Sheets: Check if any values exist in another column for each row

Question

I have a column list of "Priority" customers:

Priority
A
B
C
D

Separately, I have a range of products that various customers buy.

Product	Evaluate	Customer 1	Customer 2	...	Customer n
Drum	TRUE	C	S	...
Bell	FALSE	T	U	...	Z
Horn	TRUE	A		...

I would like to use an ArrayFormula to compute column "Evaluate" which checks whether a customer in column "Priority" exists for each row in range Customer 1 - Customer n.

Currently, I am using SEARCH for each row for each customer:

=IF(A2="","",IF(IFERROR(search('Priority'!$A$2,G2),0)
+IFERROR(search('Priority'!$A$3,G2),0)
+IFERROR(search('Priority'!$A$4,G2),0)
+IFERROR(search('Priority'!$A$5,G2),0)
...

Where G2 is a single cell in a row for my product table with comma-separated values for the customer (A, T, U...) and $A$3, $A$4, $A$5... are my priority customers in a separate column. I am dragging this formula down to evaluate each row. I'd like help coming up with a more efficient formula that doesn't require dragging down to the bottom (arrayformula would be ideal).

Hopefully that's clear - thanks!

Answer 1

Try this (assuming your Priority column is on sheet Priority!A:A):

=arrayformula({"Evaluate";if(A2:A<>"",regexmatch(query(Priority!A2:A,"",9^9),regexreplace(trim(flatten(query(transpose(C2:E),"",9^9)))," ","|")),)})

Increase the range D2:F depending on how many customer columns you have.

The likelihood is that you'll have customer names with spaces so to avoid partial regex matches, you could try:

=arrayformula({"Evaluate";if(A2:A<>"",regexmatch(char(9998)&regexreplace(regexreplace(trim(query(regexreplace(Priority!A2:A," ",char(9999)),"",9^9))," ",char(9998)),char(9999)," ")&char(9998),char(9998)&regexreplace(regexreplace(trim(flatten(query(transpose(regexreplace(C2:E," ",char(9999))),"",9^9)))," ",char(9998)&"|"&char(9998)),char(9999)," ")&char(9998)),)})