How to check if a cell contains any of an array of values?

Question

TL;DR: Using SEARCH(search_for, text_to_search), "search_for" is a variable-length array.

On Google Sheets, I have four cells, E1:E4. The values in each cell are as follows:

  |     E      |
--+------------+ 
1 |Tim         |
2 |Frank       |
3 |Tim, Frank  |
4 |Jennifer    |
--+------------+

I also have an array, which we'll call ARRAY1. The value of ARRAY1 is {Tim; Frank}.

What I'm looking for is a formula that will give me a boolean value that tells me whether or not the E cell contains any of the values from ARRAY1. In the current example, it should return {TRUE; TRUE; TRUE; FALSE}. This will then be used in a FILTER formula.

I have a nice little formula that works great if ARRAY1 contains 1 value (ie is not an array):

=arrayformula(isnumber(search(ARRAY1,E1:E4)))

For example, if ARRAY1 is {Tim}, it returns {TRUE; FALSE; TRUE; FALSE}.

However, ARRAY1 contains two values in this example. In reality, it contains a variable number of values - as does E:E.

I'm not asking to compare an array to an array, because the FILTER function automatically runs through each of E1:E4. So that can be considered to be a single reference - as far as I'm aware.

Changing ARRAY1 to {Tim; Frank} results in {FALSE; FALSE; FALSE; FALSE}.

I have no clue what I'm doing really.

Can anyone help me?

Answer 1

Short answer

Instead of SEARCH use REGEXMATCH and JOIN.

Explanation

SEARCH is for searching partial strings into other strings and if it will be used with arrays, both parameters should be arrays of the same size.

JOIN could be used to create a regular expression to be used by REGEXMATCH which in turn will return a array of TRUE/FALSE if the first parameter is an array and it is nested inside a ARRAYFORMULA.

Considering the OP examples, the formula to be used is

=ArrayFormula(REGEXMATCH(E1:E4, JOIN("|",{"Tim","Frank"})))

Answer 2

EDIT: Sorry, I didn't saw that the value of E3 includes both Tim and Frank. See my other answer.

Short answer

Instead of SEARCH use MATCH.

Explanation

SEARCH is for searching partial strings into other strings and if it will be used with arrays, both parameters should be arrays of the same size.

By the other hand, MATCH could have arrays of different sizes.

Considering the OP examples, the formula to be used is

=ARRAYFORMULA(ISNUMBER(MATCH(ARRAY1,E1:E4,0))