Question: I need help with Google Sheets to search part of a cell within a range. My formula works in Microsoft Excel but not in Google Sheets:

| A | B | C |
| --- | --- | --- |
| dog | this cat is very friendly | =LOOKUP(REPT("z",255),CHOOSE({1,2,3},"",LOOKUP(9.99999999999999E+307,SEARCH(A1:A4,B1),A1:A4))) |
| cat |  |  |
| cow |  |  |
| rabbit |  |  |

The words "dog", "cow" and "rabbit" do not appear in the in the phrase "this cat is very friendly". The word "cat" does appear in the phrase. The desired result is "cat".

How do you write a formula like this in Google Sheets?

Answer: 
try this formula:

```
=index(query(if(iserror(search(A1:A,B1)),,A1:A),"where Col1 <>''"))

```
To get the list of all words in `A1:A` that appear in the phrase in `B1`, use `filter()` and `search()`, like this:

`=filter(A1:A, search(A1:A, B1))`

Your `lookup()` formula could also be made to work, but it will only give the first match, and it seems unnecessarily cumbersome.