Search if two strings both in separate columns exist in one row in excel?

Question

How can I use the find function to see if two strings exist in excel?

EX:

row/column  A        B       C     D
       1    fly     cat    dog    fish
       2    cat    pig    horse   dog
       3    zebra  pig     cat    elephant

I want to search what rows both contains cat and dog. How can I achieve this?

I can give you a formula without using the find function. Will that work? Does case matter? — rwilson
– rwilson, Commented Jul 17, 2015 at 1:14
@pnuts - surely "complex" is a subjective term - what's complex for me might not be for you or vice versa - in my view that makes the term "complex formula development" not particularly useful, especially as those asking, normally by definition, don't know the answer! — barry houdini
– barry houdini, Commented Jul 17, 2015 at 11:54

Andy · Accepted Answer · 2015-07-17 03:55:41Z

2

How about use AND,COUNTIF function together:

=AND(COUNTIF(A1:D1,"cat"),COUNTIF(A1:D1,"dog"))

If the row contains both cat and dog,it returns TRUE.

answered Jul 17, 2015 at 3:55

Andy

9410 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

rwilson Over a year ago

This is actually the best way to write the formula.

rwilson Over a year ago

Right. When I wrote the comment here, I was more just referring to how overly complicated my formula is and how simple this formula is.

joehanna Over a year ago

@pnuts That is exactly what I did (separate search terms out) in my answer :)

joehanna Over a year ago

It was to convert a geeky number (0/1/2) into text for readability and potentially filtering

joehanna Over a year ago

Ok - I accept anchoring wasn't absolutely necessary. I also get you can produce TRUE/FALSE like that - both work equally well - but using the IF provides a more user-friendly output.

|

rwilson · Accepted Answer · 2015-07-17 01:26:19Z

1

If the formula returns a 1, the row contains both cat and dog. If it returns 0, at least one is missing from the row:

=SUMPRODUCT(MAX(--(A1:D1="cat"))*MAX(--(A1:D1="dog")))

answered Jul 17, 2015 at 1:26

rwilson

2,14513 silver badges18 bronze badges

2 Comments

Justin Over a year ago

@rwilson This doesn't seem to work. Even when I edit it for a bigger file that A:D. It just always returns 0.

rwilson Over a year ago

The formula didn't work when used on the sample above? The formula only looks for an exact match. If a row contains the word "dogs", searching for "dog" with this formula won't return a match.

HelloNewWorld · Accepted Answer · 2015-07-17 05:11:39Z

0

If you really love find function,though it seemed longer.

=AND(FIND("cat",CONCATENATE(A1,"|",B1,"|",C1,"|",D1)),FIND("dog",CONCATENATE(A1,"|",B1,"|",C1,"|",D1)))

Separator | is important!

cat + dog = catdog

ca + tdog = catdog

But add separator |(or other) will not:

cat + |+ dog = cat|dog

ca + | + tdog= ca|tdog

Note that find is case-sensitive. If not case-sensitive you can use search function.

answered Jul 17, 2015 at 5:11

HelloNewWorld

3353 silver badges11 bronze badges

Comments

joehanna · Accepted Answer · 2015-07-17 08:18:50Z

0

If you are happy to run a formula on each line, then you could copy this down and check for a value of "2".

=IF(AND(COUNTIF($A3:$D3,"cat")>0,COUNTIF($A3:$D3,"dog")>0),"Found them","")

You could replace the literals for a cell reference so that you can easily change out your search words. Maybe something like this:

=IF(AND(COUNTIF($A3:$D3,$F$1)>0,COUNTIF($A3:$D3,$G$1)>0),"Found them","")

edited Jul 17, 2015 at 8:18

answered Jul 17, 2015 at 1:35

joehanna

1,4891 gold badge11 silver badges23 bronze badges

2 Comments

barry houdini Over a year ago

But =2 potentially gives some false positives (what if there are 2 dogs) or the opposite (what if there are 2 dogs and 1 cat?)

joehanna Over a year ago

Well spotted @barryhoudini! I have revised my answer.

Collectives™ on Stack Overflow

Search if two strings both in separate columns exist in one row in excel?

4 Answers 4

6 Comments

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related