[SOLVED] vlookup with search?

[maryj]

On one sheet I have a list of complete UPC codes. On another sheet, the UPC
codes are only partial numbers. I need to do a look up to match the partial
UPC with the full UPC.
--
maryj

[Iskus23]

When using VLookups, if you leave the final field blank instead of saying
false, it will find fields that are close. See if this helps.

"maryj" wrote:

> On one sheet I have a list of complete UPC codes. On another sheet, the UPC
> codes are only partial numbers. I need to do a look up to match the partial
> UPC with the full UPC.
> --
> maryj

[maryj]

Thanks - I tried that - it doesn't work. It does not always bring in the
correct result.
--
maryj


"Iskus23" wrote:

> When using VLookups, if you leave the final field blank instead of saying
> false, it will find fields that are close. See if this helps.
>
> "maryj" wrote:
>
> > On one sheet I have a list of complete UPC codes. On another sheet, the UPC
> > codes are only partial numbers. I need to do a look up to match the partial
> > UPC with the full UPC.
> > --
> > maryj

[Iskus23]

Do you have any other fields that would match exactly? Perhaps you could do
the Vlookup using one of those.

"maryj" wrote:

> Thanks - I tried that - it doesn't work. It does not always bring in the
> correct result.
> --
> maryj
>
>
> "Iskus23" wrote:
>
> > When using VLookups, if you leave the final field blank instead of saying
> > false, it will find fields that are close. See if this helps.
> >
> > "maryj" wrote:
> >
> > > On one sheet I have a list of complete UPC codes. On another sheet, the UPC
> > > codes are only partial numbers. I need to do a look up to match the partial
> > > UPC with the full UPC.
> > > --
> > > maryj

[maryj]

I wish there was another field to use.
--
maryj


"Iskus23" wrote:

> Do you have any other fields that would match exactly? Perhaps you could do
> the Vlookup using one of those.
>
> "maryj" wrote:
>
> > Thanks - I tried that - it doesn't work. It does not always bring in the
> > correct result.
> > --
> > maryj
> >
> >
> > "Iskus23" wrote:
> >
> > > When using VLookups, if you leave the final field blank instead of saying
> > > false, it will find fields that are close. See if this helps.
> > >
> > > "maryj" wrote:
> > >
> > > > On one sheet I have a list of complete UPC codes. On another sheet, the UPC
> > > > codes are only partial numbers. I need to do a look up to match the partial
> > > > UPC with the full UPC.
> > > > --
> > > > maryj

[SteveG]

Mary,

Try this.

=SUMPRODUCT((ISNUMBER(SEARCH(A1,Sheet3!$A$1:$A$4,1)))*(Sheet3!$A$1:$A$4))

This is where A1 is your partial code and Sheet3!A1:A4 is your complete codes. Copy this down your list of partials. This also assumes that your partials will be unique to only one complete code.

Does that help?

Steve