I think this may work ok (if I understand correctly):
=MIN(IF(ISERROR(SEARCH(SheetA!$A$1:$A$50,A1)),"",
SEARCH(SheetA!$A$1:$A$50,A1)))0
(all one cell)
This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)
frosterrj wrote:
Thanks for all the replies. I think I may not have been clear. Let me
clarify to see if this is possible:
My fist book A has the partial strings, just one column, maybe 50 rows
(subset of my customer database). My second sheet B, from which I need to
compare the names in sheet A, has the whole customer database.
So what I am trying to do is use the array in sheet A to populate "true" in
sheet B when the string in sheet A matches.
Can I use the array in Sheet A with the formulas you propose?
like:
=COUNTIF(B1:B100,"*"&A1:A50&"*")0 ??? It;s the array in Sheet A that's
throwing me...
Thanks Again,
Robert
" wrote:
Dave Peterson wrote...
....
But even simpler:
=isnumber(match("*" & a1 & "*",b1:b100,0))
will return true/false
....
But even shorter still,
=COUNTIF(B1:B100,"*"&A1&"*")0
will return True/False with a single function call. Now it may not
recalc as quickly, but there are times when nested function calls must
be kept to a minimum.
--
Dave Peterson
|