Help with Index and Small formula to look up data in multiple columns

[excelnovice264]

Good morning all,

I'm hoping someone can help me with a formula i'm having a bit of trouble getting to work. Although i feel i have the foundations of the formula correct i cannot seem to get it to work as it should.

I've got a set of data which includes types of Fruit and Recipes that can be made from that fruit. Each combination of fruit and recipe is further categorised into a 2 further categories - Letters (A to J) and Numbers (1 to 8). Each combination of fruit and recipe will have a proportion of letters (that add to 1) and a proportion of numbers (that add to 1). These 2 further categories need to be treated independently so the sum of the proportion each add to 1.

I have included the data set as an attachment as it is a little tricky to explain but as an example -

Baked Apple has a proportion of Numbers as 50% to category 2 and 50% to category 5 as well as a proportion of Letters as 20% each allocated to B, C, D, E & F.

What i want to be able to do is for each combination of fruit and recipe type eg Baked Apple i want to alongside this combination list all the possible combinations of numbers and letters that are greater than 0%. For this example there are 10 possible combinations - 2 & B, 2 & C, 2 & D, 2 & E, 2 & F, 3 & B, 3 & C, 3 & D, 3 & E, 3 & F.

The attachment includes the input data set as well as the output data set which for this set of data i have created manually to show but in actual fact i will need to replicate on a much larger different set of data.

The formula i have tried uses Index as well as Small and Column/Row to try and produce what is in the output worksheet highlighted in yellow but cannot seem to pull it off.

Any help with this would be much appreciated.

[WideBoyDixon]

Based on the sheet you attached, this would re-create it I think:

Please Login or Register  to view this content.

WBD

[BMV]

I'm not sure I understood correctly but try

[excelnovice264]

Thank you both, i have just had a quick glance at both solutions and both seem to do what i wanted. I am not very knowledgeable in the way of VBA but certainly keen to learn so will have a look at this solution also. Thanks for your help

[excelnovice264]

I'm not sure I understood correctly but try

Can you tell me how the formulas would change if the headers for the number category were text rather than number. I see the formulas are dependent on these headers being a number format but cannot seem to work out how to change the formula short of using a lookup later on.

[BMV]

In this case two small changes have to be done.
COLUMN($C$1:$J$1)-2 in stand of simple $C$1:$J$1 in input!v2

Please Login or Register  to view this content.

And output!d2

Please Login or Register  to view this content.

[Bo_Ry]

Please try at
B2:C2

Please Login or Register  to view this content.

D2

Please Login or Register  to view this content.

E2

Please Login or Register  to view this content.

Or
Create Table from Input
Open Power Query/Get and Transform. Click on New Query.
Open blank query in the editor, launch Advanced Editor and paste in the following code.

Please Login or Register  to view this content.

[BMV]

Bo_Ry, good trick
(MMULT(--(Input!$C$2:$J$20>0),ROW(B$1:INDEX(B:B,COLUMNS(Input!$C$2:$J$20)))^0)*MMULT(--(Input!$K$2:$T$20>0),ROW(B$1:INDEX(B:B,COLUMNS(Input!$K$2:$T$20)))^0)>=COLUMN($A$1:$ZA$1)
However two MMULT and Small/Aggregate(15 in the many rows is hard calculation.

[excelnovice264]

Thank you very much all, 3 excellent ways of producing what i need for a much larger data set. I need to have some time to examine exactly how each of these equations work. I thought i was fairly knowledgeable with excel formulas until i have seen some of these. I may come back with some further questions