Search either of two items

  • Thread starter Thread starter Searching for an Answer
  • Start date Start date
S

Searching for an Answer

I am using a search function among a larger formula. I
want to return a number or error if either of two words
occurs in a string, but my interpretation is that you can
only have one item to search for.

For instance, I want a number to return if
either "Citigroup" or "Smith Barney" occur anywhere in a
cell.

Any suggestions?
 
Hi
try
=IF(OR(ISNUMBER(FIND("Citigroup",A1)),ISNUMBER(FIND("Smith
Barney",A1))),"within cell","not in cell")
 
How do I extend this reasoning to this formula?

=SUMPRODUCT(--(ISNUMBER(SEARCH("Citigroup",Equity!
$J$2:$J$372))),Equity!$L$2:$L$372), whereas this formula
just looks for Citigroup, I want it to also look for
Smith Barney in each cell.

My initial guess is the following:
=SUMPRODUCT(--OR((ISNUMBER(SEARCH("Smith Barney",Equity!
$J$2:$J$372))),(ISNUMBER(SEARCH("Citigroup",Equity!
$J$2:$J$372)))),Equity!$L$2:$L$372)
But this formula returns a '#value'.
 
Hi
try
=SUMPRODUCT(--((ISNUMBER(SEARCH("Smith Barney",Equity!
$J$2:$J$372)))+(ISNUMBER(SEARCH("Citigroup",Equity!
$J$2:$J$372)))>0),Equity!$L$2:$L$372)
 
Thank you. That works.
-----Original Message-----
Hi
try
=SUMPRODUCT(--((ISNUMBER(SEARCH("Smith Barney",Equity!
$J$2:$J$372)))+(ISNUMBER(SEARCH("Citigroup",Equity!
$J$2:$J$372)))>0),Equity!$L$2:$L$372)

--
Regards
Frank Kabel
Frankfurt, Germany


.
Click to expand...
 
Back
Top