Matching 2 lists

[haas.chaudhry]

Hi All,

I was wondering if I can get some help with a probelm I've
encountered. I have 2 lists - one list has a bunch of cities listed in
it, separated by space, and the 2nd list has only one city listed
there. I want to to create a column next to the first list and put in
a formula stating that one of the cities listed in the 1st list is
actually on the 2nd list. An example would be better way to describe
this:

List1
Column A, Column B
Middletown Athens Atlanta LA NY Chicago, "Yes"

List2
ColumnA
Madison
Lexington
Savannah
Danbury
Providence
Atlanta
....

So, from above, in column B of list one, my formula would look at the
cities in Column A and check against List2 column A and say "Yes" if
any one of the Cities in List1 Column A appear in List2 Column A. The
reason I have a "Yes" in Column B of list 2 is because Atlanta appears
in List2. Otherwise, it would be a "No"...

Thanks in advance.

 

[meh2030]

Hi All,

I was wondering if I can get some help with a probelm I've
encountered. I have 2 lists - one list has a bunch of cities listed in
it, separated by space, and the 2nd list has only one city listed
there. I want to to create a column next to the first list and put in
a formula stating that one of the cities listed in the 1st list is
actually on the 2nd list. An example would be better way to describe
this:

List1
Column A, Column B
Middletown Athens Atlanta LA NY Chicago, "Yes"

List2
ColumnA
Madison
Lexington
Savannah
Danbury
Providence
Atlanta
...

So, from above, in column B of list one, my formula would look at the
cities in Column A and check against List2 column A and say "Yes" if
any one of the Cities in List1 Column A appear in List2 Column A. The
reason I have a "Yes" in Column B of list 2 is because Atlanta appears
in List2. Otherwise, it would be a "No"...

Thanks in advance.

Haas,

Assuming that the text for List 1 is in A1 and the text for List 2
starts in A3, you could use the formula listed below, which I placed
in B3. The FIND formula returns whether the desired string can be
found, returning the starting character position of the matching
text. If no text is found, the FIND formula returns an error. Hence,
if an error is returned, then the List 2 city is not found in List 1,
but if an error is not returned, then the List 2 city is found in List
1.

=IF(ISERROR(FIND(A3,$A$1,1)),"No","Yes")

Best,

Matthew Herbert

 

[haas.chaudhry]

Haas,

Assuming that the text for List 1 is in A1 and the text for List 2
starts in A3, you could use the formula listed below, which I placed
in B3.  The FIND formula returns whether the desired string can be
found, returning the starting character position of the matching
text.  If no text is found, the FIND formula returns an error.  Hence,
if an error is returned, then the List 2 city is not found in List 1,
but if an error is not returned, then the List 2 city is found in List
1.

=IF(ISERROR(FIND(A3,$A$1,1)),"No","Yes")

Best,

Matthew Herbert- Hide quoted text -

- Show quoted text -

Thanks for the above but that doesn't seem to work.