Please help me turn an ugly function into a beautiful array

Joined
May 6, 2011
Messages
25
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?
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
How about

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

... confirmed with Ctrl+Shift+Enter.
 
Upvote 0
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.
 
Upvote 0
Here's a non-array function:
Code:
=IF(ISNUMBER(MATCH("*"&A2&"*",E3:E6,0)),E2,"")
 
Upvote 0
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.
 
Upvote 0
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 :confused:
Excel Workbook
ABCDE
1
2bandx
3xa
4b
5c
6d
Sheet1
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,534
Messages
6,179,390
Members
452,909
Latest member
VickiS

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top