Indexing multiple columns in Excel, return neighboring column

Question

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

I would my data in a table. such that you have a row for each meal and a column for each ingredient, that way you just sum the column to find the total of each ingredient? Maybe do this as an intermediate step between your input and output. — JeffUK
– JeffUK, Commented Jul 16, 2017 at 21:29

user6585761 · Accepted Answer · 2017-07-16 23:34:05Z

1

You can use SUMIF, where the sum range is offset from the criteria range. For example, for simplicity, let's say that C12 to H19 contains the data (ingredient and quantity), try...

=SUMIF($C$12:$G$19,B60,$D$12:$H$19)

...where B60 contains the ingredient of interest. Notice that the sum range D12:H19 is offset from the criteria range C12:G19.

answered Jul 16, 2017 at 23:34

user6585761

Sign up to request clarification or add additional context in comments.

1 Comment

Megan Over a year ago

This worked perfectly, and saved me from spending hours reorganizing my data. Thanks so much! I'll remember how to offset the SUMIF function in the future

Collectives™ on Stack Overflow

Indexing multiple columns in Excel, return neighboring column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related