If I have two named ranges, Range_1 and Range_2...

and

Cell A1 has a string that may or may not be in Range_1

and

Cell B1 has a string that may or may not be in Range_2

is there a formula that will return a YES if both strings are present in the respective named ranges or a NO if either string is not present in its respective range?

Say Range_1 is A2:A150, Range_2 is C2:J200.

Thanks,
Howard

On Wednesday, August 28, 2013 7:12:53 PM UTC-7, wrote:
If I have two named ranges, Range_1 and Range_2...



and



Cell A1 has a string that may or may not be in Range_1



and



Cell B1 has a string that may or may not be in Range_2



is there a formula that will return a YES if both strings are present in the respective named ranges or a NO if either string is not present in its respective range?



Say Range_1 is A2:A150, Range_2 is C2:J200.



Thanks,

Howard

This seems to work, and I will adjust the ranges and cells.
Sorry, I should have pondered a bit more before posting.

=IF(SUMPRODUCT(--($A$8:$A$40=B5)*($B$8:$M$40=B4))<0,"YES","NO")

Regards,
Howard

Hi Howard,

Am Wed, 28 Aug 2013 19:23:55 -0700 (PDT) schrieb :

=IF(SUMPRODUCT(--($A$8:$A$40=B5)*($B$8:$M$40=B4))<0,"YES","NO")

that only works, if both search strings are in the same row in your
ranges.
Try:
=IF(COUNT(FIND(B4,B8:M40))*COUNT(FIND(B5,A8:A40)) 0,"Yes","No")
And enter the array formula with CTRL+Shift+Enter


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Wednesday, August 28, 2013 11:14:04 PM UTC-7, Claus Busch wrote:
Hi Howard,



Am Wed, 28 Aug 2013 19:23:55 -0700 (PDT) schrieb :



=IF(SUMPRODUCT(--($A$8:$A$40=B5)*($B$8:$M$40=B4))<0,"YES","NO")



that only works, if both search strings are in the same row in your

ranges.

Try:

=IF(COUNT(FIND(B4,B8:M40))*COUNT(FIND(B5,A8:A40)) 0,"Yes","No")

And enter the array formula with CTRL+Shift+Enter


Regards

Claus B.

Excellent point!

Thanks Claus.

Regards,
Howard

On Wed, 28 Aug 2013 19:12:53 -0700 (PDT), wrote:

If I have two named ranges, Range_1 and Range_2...

and

Cell A1 has a string that may or may not be in Range_1

and

Cell B1 has a string that may or may not be in Range_2

is there a formula that will return a YES if both strings are present in the respective named ranges or a NO if either string is not present in its respective range?

Say Range_1 is A2:A150, Range_2 is C2:J200.

Thanks,
Howard

Also:

=IF((COUNTIF(Range_1,"*"&A1&"*")+COUNTIF(Range_2," *"& B1&"*"))=2,"Yes","No")

Also:

=IF((COUNTIF(Range_1,"*"&A1&"*")+COUNTIF(Range_2," *"& B1&"*"))=2,"Yes","No")

Thanks, Ron.

I am puzzled with the "*"&A1&"*"

Googled and looked in my J. Walkenbach references but can't find a "...here's what this does."

Regards,
Howard

Hi Ron,

Am Thu, 29 Aug 2013 06:58:30 -0400 schrieb Ron Rosenfeld:

=IF((COUNTIF(Range_1,"*"&A1&"*")+COUNTIF(Range_2," *"& B1&"*"))=2,"Yes","No")

if the occurrence of search string1 in Range_1 is 2 times and of search
string2 in Range_2 is none, then you will get also "Yes"
I would change it to:
=IF((COUNTIF(Range_1,A1)*COUNTIF(Range_2,B1))0,"Y es","No")


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Thu, 29 Aug 2013 18:23:33 +0200, Claus Busch wrote:

Hi Ron,

Am Thu, 29 Aug 2013 06:58:30 -0400 schrieb Ron Rosenfeld:

=IF((COUNTIF(Range_1,"*"&A1&"*")+COUNTIF(Range_2," *"& B1&"*"))=2,"Yes","No")

if the occurrence of search string1 in Range_1 is 2 times and of search
string2 in Range_2 is none, then you will get also "Yes"
I would change it to:
=IF((COUNTIF(Range_1,A1)*COUNTIF(Range_2,B1))0," Yes","No")



Ah, yes. Good point.

On Thu, 29 Aug 2013 05:44:41 -0700 (PDT), wrote:


Also:

=IF((COUNTIF(Range_1,"*"&A1&"*")+COUNTIF(Range_2," *"& B1&"*"))=2,"Yes","No")

Thanks, Ron.

I am puzzled with the "*"&A1&"*"

Googled and looked in my J. Walkenbach references but can't find a "...here's what this does."

Regards,
Howard

1. See Claus note and change as he suggested:

=IF((COUNTIF(Range_1,"*"&A1&"*")*COUNTIF(Range_2," *"& B1&"*"))0,"Yes","No")

2. In the COUNTIF criteria, wild cards are allowed. You will find that documented under HELP for the COUNTIF function.

On Thursday, August 29, 2013 5:32:51 PM UTC-7, Ron Rosenfeld wrote:
On Thu, 29 Aug 2013 05:44:41 -0700 (PDT), wrote:





Also:



=IF((COUNTIF(Range_1,"*"&A1&"*")+COUNTIF(Range_2," *"& B1&"*"))=2,"Yes","No")



Thanks, Ron.



I am puzzled with the "*"&A1&"*"



Googled and looked in my J. Walkenbach references but can't find a "...here's what this does."



Regards,

Howard



1. See Claus note and change as he suggested:



=IF((COUNTIF(Range_1,"*"&A1&"*")*COUNTIF(Range_2," *"& B1&"*"))0,"Yes","No")



2. In the COUNTIF criteria, wild cards are allowed. You will find that documented under HELP for the COUNTIF function.

Okay, thanks.

Howard