Please help me turn an ugly function into a beautiful array

Excel Ninja In Training said:

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?

Click to expand...

Excel Ninja In Training said:

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.

Click to expand...

JoeMo said:

Here's a non-array function:

Code:

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

Click to expand...

njimack said:

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

Click to expand...

Aladin Akyurek said:

This won't fit the following though:

A2: School is closed tomorrow.

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

Click to expand...