# is text from a cell in a column?

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

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  |
``````
-
gives me 508 error. I'm on OO 3.2.1 – Radek Apr 6 '11 at 6:59
@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
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):

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

-

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.

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

-
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