Vlookup using a substring for evaluation?
 

Is it possible to use the vlookup function when what you are trying to match
is a substring (partial match) of the table-array?

For example:
col a col b
1 pizza Many pizzas

I want to return a "true" when I do vlookup(a1, b1:b100,1,false), but I dont
know which function to use or where to get the lookup to find the text string
in a1.

Thanks,
Robert

Oh, and the col a is actually a range as well, if that matters...

Thanks,
Robert

"frosterrj" wrote:

Is it possible to use the vlookup function when what you are trying to match
is a substring (partial match) of the table-array?

For example:
col a col b
1 pizza Many pizzas

I want to return a "true" when I do vlookup(a1, b1:b100,1,false), but I dont
know which function to use or where to get the lookup to find the text string
in a1.

Thanks,
Robert

=vlookup("*" & a1 & "*", b1:b100,1,false)
will return "Many pizzas"
and
=not(iserror(vlookup("*" & a1 & "*", b1:b100,1,false)))
will return true/false

But even simpler:
=isnumber(match("*" & a1 & "*",b1:b100,0))
will return true/false



frosterrj wrote:

Is it possible to use the vlookup function when what you are trying to match
is a substring (partial match) of the table-array?

For example:
col a col b
1 pizza Many pizzas

I want to return a "true" when I do vlookup(a1, b1:b100,1,false), but I dont
know which function to use or where to get the lookup to find the text string
in a1.

Thanks,
Robert

--

Dave Peterson

You could enter this in C1 and copy down:

=ISNUMBER(SEARCH($A$1,B1))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"frosterrj" wrote in message
...
Oh, and the col a is actually a range as well, if that matters...

Thanks,
Robert

"frosterrj" wrote:

Is it possible to use the vlookup function when what you are trying to
match
is a substring (partial match) of the table-array?

For example:
col a col b
1 pizza Many pizzas

I want to return a "true" when I do vlookup(a1, b1:b100,1,false), but I
dont
know which function to use or where to get the lookup to find the text
string
in a1.

Thanks,
Robert

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.

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.

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