Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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
share|improve this question
    
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

1 Answer 1

up vote 1 down vote accepted

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".

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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