Find within a list that is not unique

[BeccaJ]

I'm trying to figure out the quickest way to search a column for a lis
of values and the answer may not always be exact.

For example, within a column, I want to find if there are any of a lis
companies (ABC, IBM, Sears, XYZ, etc.). Those companies may be liste
there, but not exact. For example, there may be ABC Inc. or AB
Corporation. This is a routine search.

When I use vlookup with a named range, I can only do exact matches. Fo
some reason, anyway that I try to use * does not find matches. I’v
tried the following:

=VLOOKUP(B2,Looklist,2,0) where “Looklist” refers to a named range an
B2 would be that row’s company that is being compared. Within my list
I have * on the names.

=VLOOKUP("*"&B2&"*",Looklist,2,0)

For instance, in my list, I have "Zak" and when it looks at a cell tha
says "Zak Designs", it comes back as a false finding. Those should be
match.
Any ideas?

Thank you for any help

 

[Dan E]

Becca,

I've been trying to reproduce your errors, but have been unable to do
so. Please provide the following information...

The range which LookList refers to (ie. B1:C100)
The error being returned (#N/A, #REF!, etc...)

Dan E

 

[Harlan Grove]

...
...

For example, within a column, I want to find if there are any of a list
companies (ABC, IBM, Sears, XYZ, etc.). Those companies may be listed
there, but not exact. For example, there may be ABC Inc. or ABC
Corporation. This is a routine search.

When I use vlookup with a named range, I can only do exact matches. For
some reason, anyway that I try to use * does not find matches. I’ve
tried the following:

=VLOOKUP(B2,Looklist,2,0) where “Looklist” refers to a named range and
B2 would be that row’s company that is being compared. Within my list,
I have * on the names.

=VLOOKUP("*"&B2&"*",Looklist,2,0)

For instance, in my list, I have "Zak" and when it looks at a cell that
says "Zak Designs", it comes back as a false finding. Those should be a
match.

If your B2 cell happened to have trailing spaces at the right side, it's
possible your VLOOKUP formula would return #N/A. Try

=VLOOKUP("*"&TRIM(B2)&"*",Looklist,2,0)

 

[BeccaJ]

I've tried the named range on a different worksheet or off to the sid
(D1:E6 for example). I've never had a problem with using either o
these before for an exact match and when it is an exact match, I do ge
the correct result.

I've also tried it with
=VLOOKUP("*"&TRIM(A9)&"*",looklist,2,0)

The results that I get with everything is #N/A and I have put in lot
of scenarios that should give me a match.

Other ideas