I have a column of values (column A for this example) like such:
Phytophora sojae Phytophora ramorum Cryptococcus neoformans Coccidioides posadasii
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 Coccidioides posadasii C735 delta SOWgp
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 | Coccidioides posadasii | 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