Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have some values in a column A. I want to have a formula next to each value in a column B that would tell me if text from column A is in any of the cells from columns C.

I tried match, search but didn't make it work in OpenOffice Calc

The data looks like

Column A    B     C
   1        yes   0
   2        no    1
   3        no    4
   4        yes
share|improve this question

4 Answers 4

up vote 3 down vote accepted

You'll have to combine =LOOKUP() and =IF()

The formula will be:

=IF(LOOKUP(A2;$C$2:$C$6)=A2;"Yes";"No")

This =LOOKUP() will check for A2 values from C2 to C6, and if found will return the value of A2. This is further chained with IF (A2=A2) logical comparison, and if true will return true.

Sample data:

A   |     B       |  C 
----|-------------|------
1   |    true     | 0
2   |    false    | 1
7   |    false    | 3
4   |    true     | 4
5   |    true     | 5

With your sample data:

A   |    B   |    C
----|--------|-------
1   |   Yes  |    0
2   |   No   |    1
3   |   No   |    4
4   |   Yes  |
share|improve this answer
    
gives me 508 error. I'm on OO 3.2.1 –  Radek Apr 6 '11 at 6:59
1  
@Radek - I have Novell edition which makes use of commas as delimiters instead of semi colons, have updated –  Sathya Apr 6 '11 at 7:07
    
OOo Calc uses semicolons where Excel uses commas :-) works fine now. thank you –  Radek Apr 6 '11 at 7:08
    
you're welcome @Radek –  Sathya Apr 6 '11 at 7:18

You could also use something as simple as countif (assuming your range is from C1 to C100):

=COUNTIF(C$1:C$100,A1) 

Where A1 is the first cell with the value you want to search for. Drag this down and it will work.

This returns not just whether there are matches but also counts the number of matches.

If you just want a yes/no answer, you could do:

=IF(COUNTIF(C$1:C$100,A1) > 0, "Yes", "No")

Where countif outcomes >= 1 are labelled "yes" and all those that =0 are "no".

share|improve this answer

Assuming you're using Excel, have your tried the IF() function? Something like:

=IF(A1=C1,"Text match","No match")

You can then use the fill handle in column B to fill the function down to the end of that column.

share|improve this answer
    
Updated my question so it is more visible that I use OpenOffice Calc. –  Radek Apr 6 '11 at 6:21

Assuming A1 contained the query text and C1 contained the full text you are looking within, something like the following should work in B1:

=IF(ISERR(FIND(A1,C1)),"Not a match", "Match")

In other words if looking for A1 inside C1 returns an error (no match) then set B1's value to "Not a match", otherwise A1 occurs somewhere within C1 so set B1's value to "Match".

share|improve this answer
    
updated my question, gave visual sample of how the data look like. I need to know if A1 is somewhere in any cell of column C ... –  Radek Apr 6 '11 at 6:34
    
Ah, I see what you're asking for now, @Sathya's given a great answer. This answer doesn't fit your question now but I will keep it up anyway. –  8088 Apr 6 '11 at 6:55

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.