Hello, I have scratched my head far too long and need some help.
Sheet1! column A contains short names. Sheet2! column A contains full
names. Here is what i would like to do. If the short name in Sheet1
cell A1 is found in any of the full names in Sheet2 column A then
Sheet1 cell = 'yes' otherwise = null.
Thanks,
Wes

In Sheet1 cell B1:

=if(isnumber(match(a1,sheet2!a:a,0)),"Yes","")

(and drag down)

Fester wrote:

Hello, I have scratched my head far too long and need some help.
Sheet1! column A contains short names. Sheet2! column A contains full
names. Here is what i would like to do. If the short name in Sheet1
cell A1 is found in any of the full names in Sheet2 column A then
Sheet1 cell = 'yes' otherwise = null.
Thanks,
Wes

--

Dave Peterson

Thanks Dave but that does not appear to work.
For example Sheet1! cell A1 has the following name proceeded by a
space: Mike Comrie
Somewhere in Sheet2! column A is the name: Mike Comrie C
I need this to be considered a match. ie. if whatever is in Sheet1!
cell A1 is found in any position of a cell in colum A1 of Sheet2! then
that is a match and Sheet1! cell B1 would be set to yes.
Thanks to all that help.
Wes

Excel likes exact matches--just like most mechanized things.

But maybe...
=if(isnumber(match("*"&a1&"*",sheet2!a:a,0)),"Yes" ,"")


Fester wrote:

Thanks Dave but that does not appear to work.
For example Sheet1! cell A1 has the following name proceeded by a
space: Mike Comrie
Somewhere in Sheet2! column A is the name: Mike Comrie C
I need this to be considered a match. ie. if whatever is in Sheet1!
cell A1 is found in any position of a cell in colum A1 of Sheet2! then
that is a match and Sheet1! cell B1 would be set to yes.
Thanks to all that help.
Wes

--

Dave Peterson

In .com, Fester
spake thusly:

Thanks Dave but that does not appear to work. For example
Sheet1! cell A1 has the following name proceeded by a
space: Mike Comrie
Somewhere in Sheet2! column A is the name: Mike Comrie C
I need this to be considered a match. ie. if whatever is in
Sheet1! cell A1 is found in any position of a cell in colum A1 of
Sheet2! then that is a match and Sheet1! cell B1 would be set to
yes. Thanks to all that help.

[(Dave Peterson had written:]

In Sheet1 cell B1:
=if(isnumber(match(a1,sheet2!a:a,0)),"Yes",""


Wes, in that case, use a -1 where Dave has a 0, as an arg to
the MATCH statement.

=if(isnumber(match(a1,sheet2!a:a,-1)),"Yes",""


However, you will run into problems if one name is, for example,
Steve Smith, and another (on Sheet2!) is Steve Smithson.

-dman-

How about

=IF(SUMPRODUCT(--ISNUMBER(FIND(Sheet1!A1:A100,Sheet2!A1:A3000))),"y es","no")

HTH
Kostis Vezerides

Fester wrote:
Hello, I have scratched my head far too long and need some help.
Sheet1! column A contains short names. Sheet2! column A contains full
names. Here is what i would like to do. If the short name in Sheet1
cell A1 is found in any of the full names in Sheet2 column A then
Sheet1 cell = 'yes' otherwise = null.
Thanks,
Wes

Many thanks to all who have helped.
=if(isnumber(match("*"&a1&"*",sheet2!a:a,0)),"Yes" ,"")
worked great and now I can have a good weekkend.
Wes

In .com, Fester
spake thusly:

Many thanks to all who have helped.
=if(isnumber(match("*"&a1&"*",sheet2!a:a,0)),"Yes" ,"")
worked great and now I can have a good weekkend.

Dave Peterson suggested that, and it is, indeed, clever.
But in my humble opinion it has some problems with regard
to your stated goals. You had said in Message-ID:
.com
(and I really wish you'd leave a bit of context in from
prior posts so I or others wouldn't have to go find this
stuff again!):

For example Sheet1! cell A1 has the following name proceeded
by a space: Mike Comrie Somewhere in Sheet2! column A is the
name: Mike Comrie C I need this to be considered a match. ie.
if whatever is in Sheet1! cell A1 is found in any position of
^^^^^^^^^^^^^^^
a cell in colum A1 of Sheet2! then that is a match and Sheet1!
cell B1 would be set to yes.

(Emphasis, of course, added by me.)


Well, The above formula will NOT match if there is a leading space
on "Mike Comrie" on your Sheet1, but no leading space on Sheet2.

Or did you perhaps mean by "in any position," simply, "in any row"?
In that case, then Dave's latest is good. But that's not how
I read "in any position." I thought you meant there could be no
leading space on Sheet2.

If spacing is irregular, then my suggestion building on Dave's
earlier formula still works:

=if(isnumber(match(a1,sheet2!a:a,-1)),"Yes","")

I simply changed his "0" argument to MATCH to "-1", as I already posted.

However, both Dave's latest suggestion and the one you went with
suffer from one or another version of the flaw I pointed out
already. So I don't think you should be quite ready for that
weekend scotch!

Specifically, suppose you have (to make this more personal
to this thread), in Column A of Sheet1, with leading spaces,

Dave Peters
Anny Smith
Wes Finch


and suppose you have on Sheet2 in Column A somewhe

Dave Peterson C
Manny Smith-Anderson D
Wes Finchelman E

Well, my friend, you're going to get some bum "matches"! Probably
not what you want. With Dave's latest, supposing Sheet2 *does*
also have at least one leading space, you'll match falsely on
"Dave Peters" and "Wes Finch". And if Sheet2 has no leading
spaces, you won't match on anything at all. On the other hand,
with the alternate formula I suggested, you'll get false positive
matches on all of the names in my sample list; however, the lack
of a leading space on Sheet2 won't be a problem.

I just tried Kostis Vezerides's suggestion of

=IF(SUMPRODUCT(--ISNUMBER(FIND(Sheet1!A1:A100,Sheet2!A1:A3000))),"y es","no")

and can't get it to work at all, unfortunately -- leading spaces or no.
(I also took the set ranges out and tried it with just A:A in both places.
Still no dice.)

-dman-

In , Dallman Ross <dman@localhost.
spake thusly:

However, both Dave's latest suggestion and the one you went with

Erm, I meant "both my suggestion and Dave's latest, which you
went with . . ."

-dman-