If the cell contains a row

Question

How to assign a value to cells if it contains a specific row?

For example, the fields in column A:

dog11 cat22 cow11 chick11 duck22 cat11 horse22 cat33 

The syntax in column B is:

 =IF(SEARCH("cat",A1),"cat",IF(SEARCH("22",A1),"22","none")) 

It always raises the first TRUE cell, but crashes when the value does not match true.

Answer 1

SEARCH does not return 0 ; if there is no match, it returns #VALUE! . Thus, you must wrap the SEARCH calls with IFERROR .

For example...

= IF (IFERROR (SEARCH ("cat", A1), 0), "cat", "none")

or

= IF (IF ERROR (SEARCH ("cats", A1), 0), "cats", IF (IFERROR (SEARCH ("22", A1), 0), "22", "no"))

Here IFERROR returns the value from SEARCH when it is running; the given value is 0 otherwise.

Answer 2

You can use OR() to group expressions (as well as AND() ):

 =IF(OR(condition1, condition2), true, false) =IF(AND(condition1, condition2), true, false) 

So, if you want to check "cat" and "22":

 =IF(AND(SEARCH("cat",a1),SEARCH("22",a1)),"cat and 22","none")