3

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

4 Answers 4

3

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  |
4
  • gives me 508 error. I'm on OO 3.2.1
    – Radek
    Apr 6, 2011 at 6:59
  • 1
    @Radek - I have Novell edition which makes use of commas as delimiters instead of semi colons, have updated
    – Sathyajith Bhat
    Apr 6, 2011 at 7:07
  • OOo Calc uses semicolons where Excel uses commas :-) works fine now. thank you
    – Radek
    Apr 6, 2011 at 7:08
  • Most cells work but many say #N/A. What does this mean?
    – User
    Dec 26, 2015 at 4:51
3

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

2

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.

1
  • Updated my question so it is more visible that I use OpenOffice Calc.
    – Radek
    Apr 6, 2011 at 6:21
1

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

2
  • 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, 2011 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.
    – Gareth
    Apr 6, 2011 at 6:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .