Please help me turn an ugly function into a beautiful array

[Excel Ninja In Training]

I've got a nested function that works just fine, but it's long, ugly, and hard to expand:

=IF(OR(ISNUMBER(SEARCH(E$3,$A2)),ISNUMBER(SEARCH(E$4,$A2)),ISNUMBER(SEARCH(E$5,$A2)),ISNUMBER(SEARCH(E$6,$A2))),E$2,"")

In other words, if any of the strings I've entered in cells E3 through E6 are found in cell A2, then the contents of E2 are returned in the active cell.

Like I said, this works fine, but it gets ever longer and more unwieldy the more search terms I add.

I know that the answer is an array. How would I go about transforming this into one?

 

[shg]

How about

=IF(OR(ISNUMBER(SEARCH(E$3:E$6, $A2))), E$2, "")

... confirmed with Ctrl+Shift+Enter.

 

[njimack]

Confirm with ctrl+shift+enter:
=IF(ISNUMBER(SEARCH(E3:E6,A2)),E2,"")

 

[Excel Ninja In Training]

Thank you both for your quick replies!

shg, your code works perfectly.

njimack, your code does not seem to work for me. I can't say that understand why. As I understand it, OR and ISNUMBER both return TRUE or FALSE. I was just using OR in the original ugly function to make the nesting work. But somehow, leaving it out seems to be a problem.

 

[JoeMo]

Here's a non-array function:

=IF(ISNUMBER(MATCH("*"&A2&"*",E3:E6,0)),E2,"")

 

[T. Valko]

I've got a nested function that works just fine, but it's long, ugly, and hard to expand:

=IF(OR(ISNUMBER(SEARCH(E$3,$A2)),ISNUMBER(SEARCH(E$4,$A2)),ISNUMBER(SEARCH(E$5,$A2)),ISNUMBER(SEARCH(E$6,$A2))),E$2,"")

In other words, if any of the strings I've entered in cells E3 through E6 are found in cell A2, then the contents of E2 are returned in the active cell.

Like I said, this works fine, but it gets ever longer and more unwieldy the more search terms I add.

I know that the answer is an array. How would I go about transforming this into one?

Here's another one.

Array eentered**:

=IF(E2="","",IF(COUNT(SEARCH(E3:E6,A2)),E2,""))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[njimack]

njimack, your code does not seem to work for me. I can't say that understand why. As I understand it, OR and ISNUMBER both return TRUE or FALSE. I was just using OR in the original ugly function to make the nesting work. But somehow, leaving it out seems to be a problem.

Seems to work for me

Excel Workbook
	A	B	C	D	E
1
2	band				x
3		x			a
4					b
5					c
6					d
Sheet1

 

[Aladin Akyurek]

Here's a non-array function:

=IF(ISNUMBER(MATCH("*"&A2&"*",E3:E6,0)),E2,"")

This won't fit the following though:

A2: School is closed tomorrow.

E3: Yesterday
E4: Morning
E5: Tomorrow
E6: Noon

 

[T. Valko]

Seems to work for me

Excel Workbook
	A	B	C	D	E
1
2	band				x
3		x			a
4					b
5					c
6					d
Sheet1

It's because the first element of the search array just happpens to be in the string.

Change cell A2 to Bob or dirt or uncle.

 

[JoeMo]

This won't fit the following though:

A2: School is closed tomorrow.

E3: Yesterday
E4: Morning
E5: Tomorrow
E6: Noon

Correct. What I posted is intended to find the string in A2 anywhere within the strings in E3:E6.