dimanche 16 juillet 2017

Indexing multiple columns in Excel, return neighboring column

I am working on an excel sheet that will help me with meal-planning. Currently, I have my data stored in a manner like demonstrated below:

                  **Monday**
 Breakfast        Bagel         1       Cream Cheese    1        
 Lunch            Spinach       1       Eggs            4       Cream Cheese 1      
 Dinner           Pork          1       Eggs            2

                  **Tuesday**
 Breakfast        Cereal        2       Milk            1       Eggs         3
 Lunch            Bagel         1       Butter          1
 Dinner           Eggs          2

CURRENT OUTPUT:

 Ingredients Needed:
 Eggs           2
 Bagel          2
 Spinach        1
 Pork           1
 Cereal         2
 Milk           0
 Butter         0
 Cream Cheese   0

Where the numbers represent the number of servings of each ingredient I need to buy.

The problem I am facing is that my current formula:

=INDEX($C$12:$C$58, SMALL(IF(ISNUMBER(SEARCH($B$60, $B$12:$B$58)), MATCH(ROW($B$12:$B$58), ROW($B$12:$B$58))), ROW(B1)))

Only indexes one row at a time. For example, if I am searching for how many Eggs I need to buy for the week, the formula would return 2 eggs (because it is only searching column B for Eggs and returning column C).

Is there a work-around that would allow me to be able to return the full number of ingredients I need to buy? I realize I could index each column individually, however, since I have many ingredients (100+), using my current formula doesn't seem feasible.

Any help is massively appreciated

Aucun commentaire:

Enregistrer un commentaire