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

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

The formula will be:


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  |
| 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 – Sathyajith Bhat 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
  • Most cells work but many say #N/A. What does this mean? – User Dec 26 '15 at 4:51

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


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

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

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

| 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. – Gaff Apr 6 '11 at 6:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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