“Yes” if a value partially matches in a range of values, blank otherwise

I have a column of values (column A for this example) like such:

``````Phytophora sojae
Phytophora ramorum
Cryptococcus neoformans
``````

And I'd like to create a new formula column (B) that has the value "Yes" if the value in A is present in another column (C) that might look like:

``````Cryptococcus neoformans var. neoformans
``````

So that the spreadsheet will ultimately look like:

``````A                       | B   | C
Phytophora sojae        |     | Cryptococcus neoformans var. neoformans
Phytophora ramorum      |     | Coccidioides posadasii C735 delta SOWgp
Cryptococcus neoformans | Yes |
``````

I think I need a formula that uses some combination of FIND, FILTER, and IF; however, the FIND function does not seem to work as I expect it to.

If I can figure out how to partially match against a range, it might be sufficient, because I think I can use this solution `B1 = IF(COUNTA(IFERROR(FILTER(A:A ; A:A=C1 ))) ; "Yes" ; "")` to map it to a Yes or blank value.

In Python, the equivalent operation might look like:

``````for i, query in enumerate(A):
for target in C:
if query in target:
B[i] = "Yes"
break
``````
-
How does `A1=C1`? – Jacob Jan Tuinstra Apr 19 '13 at 6:36
Did my solution help you? – Jacob Jan Tuinstra Apr 28 '13 at 18:19
The elements of A are substrings of C, so that it partially matches. – Edd Apr 29 '13 at 23:52

This formula should do the trick:

`=ARRAYFORMULA(IFERROR(IF(VLOOKUP(A:A;C:C;{1}*SIGN(ROW(A:A));0)=A:A;"Yes";"");""))`

Explained from the inside out:

• the `VLOOKUP` will lookup column A in column C
• the `IF` statement will result "Yes" if something has been found
• the `IFERROR` function will ignore empty cells
• the `ARRAYFORMULA` will take on complete column ranges

In your example, column B is completely empty. If you add `A1` to `C4` then `B1` will give "Yes".

-
Sorry, I don't have the reputation to vote you up. I was looking for a partial match solution. "Yes" if A_n is a substring of any row in C. I think your solution is an exact matching solution. – Edd Apr 29 '13 at 23:53
I will revise the answer. – Jacob Jan Tuinstra Apr 30 '13 at 6:28
I'm working on a script to do the work for you. – Jacob Jan Tuinstra May 15 '13 at 6:42