In 2-dimensional array, find all instances of a value, and sum corresponding values in another column (column locations may vary)

[diversification]

Hello, I'm not very good at describing what I'm trying to do, so I'm going to lead with screens of my Sheets, and then describe what's I'm trying to do from there. I apologize if this is a roundabout way to ask my question.

Sheet1:

Sheet2:

The Function I'm trying to write will be to tally the "Count" in Sheet1, Column B. The Function will look at Sheet1, Column A to get the lookup value (a fruit name) and then look for all matches in Sheet2, and for any matches it finds, it'll sum up the corresponding values in the "Number" column (located at Sheet2 Column D in this example.)

If I know where the Fruit and Number columns will be located in Sheet2 ahead of time, I can write a very simple SUMIF Function that I can fill down (if fact I did for this example.) ---> =SUMIF(Sheet2!B:B,Sheet1!A2,Sheet2!D:D)

However, I unfortunately do not know the locations of the columns ahead of time since the data source sometimes will vary, and in practice, changing the formula to fit the data, or re-arranging the data is far from ideal. So for example, sometimes on Sheet2, the "Fruit" entries may be in Column C, while the "Number" entries may be in Column F (or vice versa, etc, etc.)

I was thinking perhaps an index / match, but I can't get that to work. Any help would be very much appreciated.

 

[Fluff]

Hi & welcome to MrExcel.
How about
=SUMIFS(INDEX(Sheet2!A:L,,MATCH("Number",Sheet2!A1:L1,0)),INDEX(Sheet2!A:L,,MATCH(A1,Sheet2!A1:L1,0)),A2)

 

[diversification]

Haven't tried it, but I'm guessing that'll work just from looking at the differences in syntax from what I tried. I think my problem was that I have misunderstood the proper way to use the MATCH function. I was having it look through the entirety of the range I was Indexing (A:L) instead of limiting it to one row (A1:L1.) I'll try this solution and let you know.

 

[diversification]

Ok, so this works if I know what row the headings will be in, so thank you! To make this formula even more generic though, is there a way that I could have it first identify which row the headers are in, and then use that to plug into the formula? Is that a lot more complex?

 

[Fluff]

You would need to use structured tables for that.