If either of two conditions met (partial text string), return text A or text B

[Churchy LaFemme]

My table may have either This or That in column B.

Column B
This thing
That thing
Ain't no thang
And another thing
Are you going to eat that
This I no big deal
That is a lie

I need a formula in Column C which will return "Eureka" if either "this" or "that" is in column b.

For some reason my efforts to use a nested if with wildcards is a big fat fail. (It's as if using * in a nested if breaks Excel.)

I can get a TRUE or FALSE result with this formula.

=OR(ISNUMBER(SEARCH({"*This*","*That*"},B2]])))

What can I do to change TRUE to Eureka?

Or is there another way?

(Found at least two similar questions - but was unable to return to them after logging in.)

 

[bbott]

There may be a better way, but this works:

=IF(ISNUMBER(IFERROR(SEARCH("This",B2,1),SEARCH("That",B2,1))),"Eureka","")

 

[Momentman]

This is one way

Excel 2010
	B	C
1	This thing	Eureka
2	That thing	Eureka
3	Ain't no thang	Not found
4	And another thing	Not found
5	Are you going to eat that	Eureka
6	This I no big deal	Eureka
7	That is a lie	Eureka
Sheet9

Cell Formulas
Range	Formula
C1	=IF(OR(IFERROR(SEARCH("This",$B1)>0,0),IFERROR(SEARCH("That",$B1)>0,0)),"Eureka","Not found")

 

[Stildawn]

This works, don't know if it is what you want:

=IFERROR(IF(FIND("that",B1,1)>0,"Eureka",""),"Nope")

 

[blaow]

I was working on this one and ended up with something very similar to the other answers here, however it wasn't working. I didn't have any IFERROR in my expression. Why is that needed?

 

[Momentman]

I was working on this one and ended up with something very similar to the other answers here, however it wasn't working. I didn't have any IFERROR in my expression. Why is that needed?

When the search doesn't find the expression, it returns #Value (an error) and messes up the IF statement, so the IFERROR is to cater for such instances

 

[Churchy LaFemme]

There may be a better way, but this works:

=IF(ISNUMBER(IFERROR(SEARCH("This",B2,1),SEARCH("That",B2,1))),"Eureka","")

This works.

What role is ISERROR playing?

Thank you!

 

[Churchy LaFemme]

Never mind - I see the role of ISERROR has been addressed.

 

[blaow]

When the search doesn't find the expression, it returns #Value (an error) and messes up the IF statement, so the IFERROR is to cater for such instances

Thank you, good to know.

 

[Churchy LaFemme]

Thank you for explaining the role of IFERROR and the formula.