Returning data in a list

  • Thread starter Thread starter Clueless
  • Start date Start date
C

Clueless

I have a spreadsheet with a list of product numbers, and
with each product number a product ingedient number. There
may be several product ingredient numbers to a product
number, so to save time it's set up as, say

PRODUCT # PRODUCT INGREDIENT #
12345 00001
12345 00002
12345 00003
12346 00001
12346 00002
12346 00003

and so on. Is there a way I can produce another Excel
spreadsheet where I can type in the product #, and Excel
will automatically look up all values in the Product
Ingredient # that coorespond to it, and make list them in
a column?

Thanks
 
Assuming your product list = product, ingredient list =
ingred, and A1 is where the product is entered, try this
in B1:

=INDEX(ingred,SMALL(IF($A$1=product,ROW(product)),ROW()))

and press ctrl/shift/enter. Now copy this formula down as
far as you need. When you start to see error returns, it
means that the product in A1 doesn't have any more
ingredients. Copy the formula down as far as needed - if a
product will never have more than 12 ingredients, copy it
down to row 12.

HTH
Jason
Atlanta, GA
 
I'm on my way, and that was a big help. Just one question,
however, that's what I put in the parentheses in the last
part of that formula, "ROW()."

Thanks again.
 
Back
Top