VLOOKUP/HLOOKUP to Return Several Results

[djDaemon]

I'm building a spreadsheet that will allow customers to select a product
number from a drop-down list, which will cause the components for that
product to be listed underneath.

For example, in cell B3 on sheet 1, they select a product number, which I
want to then match to a master list on sheet 2. On sheet 2, the components
(of which there are several) for that product will be listed underneath the
product number:

Sheet 2 (aka "master list"):
A B C
Product No. 52
Item #1 54769 Hex Screw
Item #2 32285 Flange
Item #3 12514 Washer
Item #4 33696 Flexible Shunt

So, sheet 2 contains these items, which are all components for Product No.
52. On sheet 1, the customer will select from a drop-down list, and upon
selecting, for example, Product No. 52, the rows below will be populated with
the appropriate components.

Due to the manner in which this information is imported into Excel, the data
on sheet 2 needs to be in this format, as opposed to listing each product in
a single row.

Any help would be greatly appreciated. Thanks!

 

[smartin]

I'm building a spreadsheet that will allow customers to select a product
number from a drop-down list, which will cause the components for that
product to be listed underneath.

For example, in cell B3 on sheet 1, they select a product number, which I
want to then match to a master list on sheet 2. On sheet 2, the components
(of which there are several) for that product will be listed underneath the
product number:

Sheet 2 (aka "master list"):
A B C
Product No. 52
Item #1 54769 Hex Screw
Item #2 32285 Flange
Item #3 12514 Washer
Item #4 33696 Flexible Shunt

So, sheet 2 contains these items, which are all components for Product No.
52. On sheet 1, the customer will select from a drop-down list, and upon
selecting, for example, Product No. 52, the rows below will be populated with
the appropriate components.

Due to the manner in which this information is imported into Excel, the data
on sheet 2 needs to be in this format, as opposed to listing each product in
a single row.

Any help would be greatly appreciated. Thanks!

Is your part data actually organized as you show above, i.e., with
product number as a "header" and part numbers underneath? If so, I think
you are going to have a very hard time with this. It would be much
better if /every/ part number row had a product number ID, then we could
address some filtering techniques.

With that said, how extensive is this list of products/parts? Could it
not be the case that a Washer is a part for multiple products? You may,
perhaps, need a proper relational database to do this effectively.

 

[djDaemon]

Yes, natively, the data is organized like that, but we're going to go ahead
and transpose the data, so as to have unique rows for each product, similar
to how a database is set up.

And no, as much as I would LOVE to use a database for this application, its
not going to happen. My fellow employees are just now getting accustomed to
using spreadsheets, and I'm afraid a database would blow their minds.

Thanks for your help!

 

[smartin]

Yes, natively, the data is organized like that, but we're going to go ahead
and transpose the data, so as to have unique rows for each product, similar
to how a database is set up.

And no, as much as I would LOVE to use a database for this application, its
not going to happen. My fellow employees are just now getting accustomed to
using spreadsheets, and I'm afraid a database would blow their minds.

Thanks for your help!

Ok -- do I understand correctly, you will have data organized like this?

Prd -- ItemID ItemDesc
52 Item #1 12345 Hex Screw
52 Item #2 23456 Flange