A find formul looking at two named ranges for two strings

  • Thread starter Thread starter lhkittle
  • Start date Start date
L

lhkittle

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
 
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
Click to expand...

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 (e-mail address removed):
=IF(SUMPRODUCT(--($A$8:$A$40=B5)*($B$8:$M$40=B4))<>0,"YES","NO")
Click to expand...

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.
 
Hi Howard,



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






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
Click to expand...
Regards

Claus B.
Click to expand...

Excellent point!

Thanks Claus.

Regards,
Howard
 
Also:
=IF((COUNTIF(Range_1,"*"&A1&"*")+COUNTIF(Range_2,"*"& B1&"*"))>=2,"Yes","No")
Click to expand...

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")
Click to expand...

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")


Regards
Claus B.
 
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.
Click to expand...

Okay, thanks.

Howard
 
Back
Top