Search for character strings in an array?

  • Thread starter Thread starter Ted Horsch
  • Start date Start date
T

Ted Horsch

I have two lists:

List one
AAA
BBB
CBA
AAX
DCC
BDD
BBD


List two
A
CCC
BB

I need a formula in column B of list one that returns a
Yes or No answer. The formula should ask: Are any of the
character strings in list two found in the current row,
column A cell in list one? Here's what list one should
look like:

List one
AAA Y
BBB Y
CBA Y
AAX Y
DC N
B N
BDD N
BBD Y

I'm a reasonably advanced excel user but I'm not a VB
programmer. I'm using Office XP on W2KPro.

Thanks.
 
Are you sure you put your question right? Suddenly in your required result
list 1 looks quite different.
I can't see how you get to the suggested answers.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Hi
not quite sure but maybe the following in B1 of your first list will do
=IF(COUNTIF(List_two,"*" & LEFT(A1,1) & "*" ) + COUNTIF(List_two,"*" &
MID(A1,2,1) & "*") + COUNTIF(List_two,"*" & RIGHT(A1,1) &
"*")>0,"Y","N")

note: works only for 3 characters in list one
 
Hi Ted

The following formula should do what you need

=IF(ISERROR(MATCH(A1,$A$11:$A$13,0)),"N","Y"

A1 is the lookup valve. $A$11:$A$13 would be the range containing table 2, this should be an absolute reference

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Ted Horsch wrote: ----

I have two lists

List one
AA
BB
CB
AA
DC
BD
BB


List tw

CC
B

I need a formula in column B of list one that returns a
Yes or No answer. The formula should ask: Are any of the
character strings in list two found in the current row,
column A cell in list one? Here's what list one should
look like

List on
AAA Y
BBB
CBA
AAX
DC
B
BDD
BBD

I'm a reasonably advanced excel user but I'm not a VB
programmer. I'm using Office XP on W2KPro

Thanks
 
The following formula should do what you need:

=IF(ISERROR(MATCH(A1,$A$11:$A$13,0)),"N","Y")

A1 is the lookup valve. $A$11:$A$13 would be the range containing table 2,
this should be an absolute reference.
Click to expand...
...

If A1 is AAA, and AAA appears nowhere in A11:A13, why do you believe your
formula above would give "Y" in cell B1? Did you really test this formula and
reporduce all the expected results the OP gave?
----- Ted Horsch wrote: -----
Click to expand...
...
 
not quite sure but maybe the following in B1 of your first list will do
=IF(COUNTIF(List_two,"*" & LEFT(A1,1) & "*" ) + COUNTIF(List_two,"*" &
MID(A1,2,1) & "*") + COUNTIF(List_two,"*" & RIGHT(A1,1) &
"*")>0,"Y","N")

note: works only for 3 characters in list one
Click to expand...
...

Clear sign there must be a better way.

B1:
=IF(SUMPRODUCT(COUNTIF(A1,"*"&ListTwo&"*")),"Y","N")

filled down.
 
Thanks very much for the responses.
Mark, I can't get your wonderfully simple solution to
work. Everything comes back as N, meaning that none of
the strings are being found even though they should be. I
think you may have the question reversed. I'm not trying
to find the string in list one cell A1 in list two, I am
trying to find any of the stings in list two in list one
cell A1. See additional explanation below.
Frank, I can't use your solution because in reality I have
up to 15 characters in both tables (I used three in my
example just to be simple).
Niek, You're right, list one, column one in all occurances
should be:
List one
AAA
BBB
CBA
AAX
DC
B
BDD
BBD

List one
AAA Y because the string A in list two is found in AAA
BBB Y because the string BB in list two is found in BBB
CBA Y because the string A in list two is found in CBA
AAX Y because the string A in list two is found in AAX
DC N because A, BB and CCC are all not found in DC
B N because A, BB and CCC are all not found in B
BDD N because A, BB and CCC are all not found in BDD
BBD Y because the value BB in list two is found in BBD

Any more suggestions?

Thanks,

Ted
 
Harlan Grove wrote:
....
Clear sign there must be a better way.

B1:
=IF(SUMPRODUCT(COUNTIF(A1,"*"&ListTwo&"*")),"Y","N")

filled down.
Click to expand...

Can't argue against that :-)

Frank
 
BINGO! Harlan, your solution works like a charm. One
curiosity: I just entered the formula as you gave it, and
it works perfectly, but I thought that you had to use
ctrl+shift+enter when using sumproduct. How come it's not
needed in your solution.

Many thanks to all of you for helping me.

Ted
 
Hi Ted
SUMPRODUCT already acts like an array function so you should never need to array enter it

By the way, I misinterpreted your question. I thought you were only looking for full matches. I didn't catch that you wanted a yes if a value in table 2 was found WITHIN a value in table 1. Sorry to send you off in the wrong direction

Glad you got it working

Regards
Mark Graesse
(e-mail address removed)
Boston M

----- Ted Horsch wrote: ----

BINGO! Harlan, your solution works like a charm. One
curiosity: I just entered the formula as you gave it, and
it works perfectly, but I thought that you had to use
ctrl+shift+enter when using sumproduct. How come it's not
needed in your solution

Many thanks to all of you for helping me

Te
 
Thanks again. I wish I was half as smart as you guys...

Ted
-----Original Message-----
Hi Ted,
SUMPRODUCT already acts like an array function so you
Click to expand...
should never need to array enter it.
By the way, I misinterpreted your question. I thought
Click to expand...
you were only looking for full matches. I didn't catch
that you wanted a yes if a value in table 2 was found
WITHIN a value in table 1. Sorry to send you off in the
wrong direction.
 
Back
Top