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
can also be of some value to Chris.
Aladin I'd go with IML's suggestion and use an AutoFilter
