MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Lookup Multiple Occurrence


Posted by Chris Wheeler on May 17, 2001 9:59 AM

I Would Like To Know How To Return Multiple Values.

Here Is A Short Example.

Column A Is Part Numbers
Column B Is Product Description

I Have Been Using Vlookup To Search Column A For A Part Number Match And Then Return The Description.

My Problem Is That I Have Many Part Numbers That Are The Same.

Vlookup Only Returns The Description From The First Part Number It Finds.

Is There A Way To Search Through An Entire Worksheet And Return All Of The Matching Results?


Posted by IML on May 17, 2001 10:14 AM

Is Autofilter an option?

This would seem the easiest way if you don't need a formula.

Posted by Mark W. on May 17, 2001 10:17 AM

Your request violates all notions of relational design.
How can a unique Part Number have multiple Descriptions?

Posted by Chris Wheeler on May 17, 2001 10:26 AM

Mark,

We Sell Many Different Brands Of Automotive Parts.
As An Example:

Wagner/Cooper Corporation Has A Part Number 323, This Is A Flasher.

Whitaker Cable Company Also Has A Part Number 323, This Is A Battery Cable.

The File I Am Searching Through Has 37,000 Records & Many Of These Are Duplicate Part Numbers With Different Descriptions & Applications.

I Hope This Helps.

Thanks

Posted by Mark W. on May 17, 2001 10:33 AM

Now were getting somewhere...

The your lookup table is actually "keyed on" (column A),
=Brand&'Part Number', and your VLOOKUP() formula
should look like....

=VLOOKUP(Brand&'Part Number',A1:B100,2,0)

Posted by Chris Wheeler on May 17, 2001 10:40 AM

Re: Now were getting somewhere...

Mark,

Here Is The Problem With That Solution:
Our Client Sends In A Usage File With All Of The Part Numbers That They Use. The File Doesn't Say What Brand It Only Has A Part Number.

If I Can Run Their Numbers Against My Master File I Can See All Of The Different Applications Of Their Part Numbers & Then Select The Appropriate Number For Their Use.

The your lookup table is actually "keyed on" (column A),

Posted by Mark W. on May 17, 2001 10:44 AM

Re: Now were getting somewhere...

I see... and, they leave it up to you to decide if
you're gonna ship a Flasher or a Battery Cable? Here Is The Problem With That Solution:

Posted by Chris Wheeler on May 17, 2001 10:48 AM

Re: Now were getting somewhere...

That's Right.

I Just Can't Seem To Get Anything To Work Here.
I Wrote A Program In Perl That Works Well For Multiple Rows But I Just Can't Work It In Excel.

I see... and, they leave it up to you to decide if

Posted by Mark W. on May 17, 2001 10:55 AM

In that case...

I'd go with IML's suggestion and use an AutoFilter
on the Part Number. Unless, of course, you can
key your lookup table on Usage&'Part Number'. That's Right. I Just Can't Seem To Get Anything To Work Here.

Posted by Aladin Akyurek on May 17, 2001 11:04 AM

Re: In that case...

The 3rd alternative described at

15934.html

can also be of some value to Chris.

Aladin I'd go with IML's suggestion and use an AutoFilter