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?

 

[Aladin Akyurek]

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

Enter xvfc in A2...

ISNUMBER(SEARCH(E3:E6,A2))

does not return a scalar. That's why the formula would fail.

 

[Excel Ninja In Training]

Here are my results so far. The arrays from shg and T. Valko work like a charm, but njimack's array still isn't working for me, and neither is JoeMo's non-array function.

I was putting together a screenshot showing myself using njimack's array and it not working; however, it looks like the conversation has progressed in the meantime. Thank you, T. Valko and Aladin.

As for your function, Joe, what I was looking for is whether the strings in E3:E6 appear anywhere in A2, rather than the reverse.

Thank you all again for your help! This is a great board.

 

[T. Valko]

Here are my results so far. The arrays from shg and T. Valko work like a charm, but njimack's array still isn't working for me, and neither is JoeMo's non-array function.

I was putting together a screenshot showing myself using njimack's array and it not working; however, it looks like the conversation has progressed in the meantime. Thank you, T. Valko and Aladin.

As for your function, Joe, what I was looking for is whether the strings in E3:E6 appear anywhere in A2, rather than the reverse.

Thank you all again for your help! This is a great board.

You're welcome. Thanks for the feedback!