Find populated cells in list of products and their ingredients and weights

Originally Posted by ChemistB

Here's a solution (I think). I made two dynamic named ranges (they will change size as you add products). "Products" which would be your list of products in column A of sheet1 and "Ingredients" which would cover the range from X4:WS? of your ingredient amounts.

Then in Sheet2, I used Data Validation to create the dropdown in A1 and used Array formulas to bring back the ingredients, units of measure and amounts. They are respectively (in B2,B3 and B4)

Formula:

=IFERROR(INDEX(Sheet1!$X$3:$WS$3, SMALL(IF(ISNUMBER(INDEX(Ingredients,MATCH($A1,Products,0),)), COLUMN($X$4:$WS$4)-COLUMN($W$4)),COLUMN(A1))),"")



=IFERROR(INDEX(Sheet1!$X$2:$WS$2, SMALL(IF(ISNUMBER(INDEX(Ingredients,MATCH($A1,Products,0),)), COLUMN($X$4:$WS$4)-COLUMN($W$4)),COLUMN(A1))),"")



=IFERROR(INDEX(Ingredients, MATCH($A1,Products,0),SMALL(IF(ISNUMBER(INDEX(Ingredients,MATCH($A1,Products,0),)), COLUMN($X$4:$WS$4)-COLUMN($W$4)),COLUMN(A1))),"")

Is this what you were looking for?

Hi chemist,

First of all thank you so much for responding. It looks like you have solved this problem! However, as a somewhat new user of excel, I'm pretty confused by the formula you've used and therefore I'm having trouble implementing your solution. We've tried to break your formula down and understand it, but we've had trouble. If you have a moment do you mind explaining what's going on in the super-formulas you used?

Thank you,
Mr. Euphorium