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!

 

[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!