4

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?

  • 1
    Please use the "Contact Us" link at the bottom of the page to merge your two accounts. Thanks! – jonsca Mar 28 '16 at 23:08
2

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"})))
  • I'm awaiting an account merger and still can't comment on your answer, sorry. This is in response to the second one. I've converted {"Tim";"Frank"} to {"Tim","Frank"} using the TRANSPOSE function, slotted that into your REGEXMATCH formula and hey presto it works perfectly. Thank you very much! All of the kudos to you. – snazzybouche Mar 28 '16 at 23:39
2

EDIT: Sorry, I didn't saw that the value of E3 includes both Tim and Frank. See my [other answer](https://webapps.stackexchange.com/a/91342/88163

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))
  • this is OP having just made an account. Rubén, I'm unable to comment on your suggestion, but it's not giving me the results I'd like. It's producing the array {TRUE; TRUE} when what I want is {TRUE; TRUE; TRUE; FALSE}. Using MATCH necessitates that the value I'm searching for is equal to the exact content of the cell. However, I'm looking so see if any value from ARRAY1 is contained within any of E1:E4. – snazzybouche Mar 28 '16 at 22:59
  • See webapps.stackexchange.com/questions/91339/… – user79865 Mar 28 '16 at 23:03
  • @snazzybouche: Sorry, I didn't saw that the value of E3 includes both Tim and Frank. See my other answer. – Rubén Mar 28 '16 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.