Nesting MATCH and INDEX formula within a SUMPRODUCT
As the title suggests, I am looking for a way to combine the SUMPRODUCT functionalities with an INDEX and MATCH formula, but if a better approach exists to help solve the problem below I am also open to it.
In the below example, imagine that the tables are on different sheets. I have a report that has the sales of each ID in the rows and each month in the columns (first table). Unfortunately, the report only has IDs and not the region they belong to, but I do have a look up table which labels each ID with their respective region (second table):
| A | B | C | D | |
|---|---|---|---|---|
| 1 | ID | January | February | March |
| 2 | 1 | 10 | 5 | 20 |
| 3 | 3 | 5 | 5 | 10 |
| 4 | 7 | 0 | 10 | 5 |
| 5 | 14 | 10 | 25 | 5 |
| 6 | 25 | 5 | 10 | 10 |
| 7 | 27 | 10 | 10 | 10 |
| 8 | 44 | 5 | 5 | 5 |
| A | B | |
|---|---|---|
| 1 | ID | Region |
| 2 | 1 | East |
| 3 | 3 | East |
| 4 | 7 | Central |
| 5 | 14 | Central |
| 6 | 25 | Central |
| 7 | 27 | West |
| 8 | 44 | West |
My goal is to be able to aggregate the sales by region as per the result below. However I would only like to show sales data that belong to the month that is shown in cell D2.
Goal:
| A | B | C | D |
|---|---|---|---|
| 1 | Region | Sales | February |
| 2 | East | 10 | |
| 3 | Central | 45 | |
| 4 | West | 15 |
I have used the INDEX and MATCH combination to return a single value, but not sure how I can return multiple values with it and aggregate them at the same time. Any insight would be appreciated!
1 Answer
Use INDEX/MATCH to return the correct column to a SUMIFS.
The SUMIFS returns an array of numbers to the SUMPRODUCT that we filter with a Boolean:
=SUMPRODUCT(SUMIFS(INDEX(Sheet1!$B:$D,0,MATCH($D$1,Sheet1!$B$1:$D$1,0)),Sheet1!A:A,Sheet2!$A$2:$A$8)*(Sheet2!$B$2:$B$8=A2))Note: Sheet1 and Sheet2 are your first and second tables respectively. You will need to change the names to your correct sheet names.
