Extracting data from a table with multiple rows and columns

Hi All,

I have a table with 142 ingredients in column 1 and then numerous spice mixes in the top row with corresponding ingredient weights manually inputted under each spice mix. Obviously not all spice mixes include all the underlying spices so there are many blanks under each spice mix. I need to create a formula that will look up the spice mix and return the underlying ingredients with corresponding weights. I don't want to use filters as the number of spice mixes will grow. I would also like the output to only show the ingredients under the relevant spice mix and leave out all blanks, thus a small table of only the underlying ingriendents and weights.

I have attached a small section of the spreadsheet below.

I would be grateful for your help.

Many thanks,
BranAlex

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Hi & welcome to the board.
How about, in F7
=IFERROR(INDEX($A$2:$A$143,AGGREGATE(15,6,(ROW($A$2:$A$143)-ROW($A$2)+1)/(INDEX($B$2:$C$143,,MATCH($F$6,$B$1:$C$1,0))<>""),ROWS(F$7:F7))),"")
In G7
=IFERROR(INDEX($B$2:$C$143,MATCH(F7,$A$2:$A$143,0),MATCH($F$6,$B$1:$C$1,0)),"")

Or if you have dynamic arrays
In F7 only (put the word List in A1)
=FILTER(FILTER(A2:C143,(A1:C1=F6)+(A1:C1="list")),INDEX(B2:C143,0,MATCH(F6,B1:C1,0))<>0)

Hi Bran,

No formulas needed!! Just put the word "Ingredients" in A1 and do a Pivot Table. You'll need to filter the Pivot by Values that are greater than zero and sort from small value to large value.

See the attached: Spice Recipe Analyses Pivot Solution.xlsx

Time to learn more about Pivot Tables?

No formulas - No Pivots - No manipulation
Table self expanding - just put your new mix name in header or new extra ingredients/quantities in rows.
Do not forget to press the green button first.
torachan.