Fester said:
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:
<
[email protected]>
(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 somewhere:
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))),"yes","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-
