Note, I have edited my original question to clarify my problem:
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!