Summing an array of values based on multiple criteria and look up table

Question

I am given the following sales table which provide the sales that each employee made, but instead of their name I have their ID and each ID may have more than 1 row.

To map the ID back to the name, I have a look up table with each employee's name and ID. One thing to keep in mind is that any given name could potentially have more than one ID assigned to it, as described in the example below:

Sales Table:

Year	ID	North	South	West	East
2020	A	58	30	74	72
2020	A	85	40	90	79
2020	B	9	82	20	5
2020	B	77	13	49	21
2020	C	85	55	37	11
2020	C	29	70	21	22
2021	A	61	37	21	42
2021	A	22	39	2	34
2021	B	62	55	9	72
2021	B	59	11	2	37
2021	C	41	22	64	47
2021	C	83	18	56	83

ID table:

ID	Name
A	Allison
B	Brandon
C	Brandon

I am trying to sum up each employee's sales by a given year, and aggregate all their transactions by their name (rather than ID), so that my result looks like the following:

Result:

Report
2021
Allison	258
Brandon	721

I want the user to be able to select the year, and the report would automatically sum up each person's sales by the year and their name. Again, Brandon was assigned ID B and C, so the report should be able to obtain all 2021 sales under B and C.

I posted a similar question which did not include the added complexity of having a name tied to more than one ID. In that thread, I was provided a solution with the following formula:

=SUMPRODUCT($C$2:$F$13*($B$2:$B$13=INDEX($I$2:$I$4,MATCH(N3,$J$2:$J$4,0)))*($A$2:$A$13=$N$2))

While this formula works on names that only have one ID tied to it, I believe the INDEX and MATCH component falls through once it encounters a duplicate name on the ID table.

I am currently using Excel 2016, so any solution would need to be compatible with that version at least. Thanks in advance for any guidance on this.

Answer 1

Try this formula solution can work in your Excel 2016

In L4, formula copied down :

=SUMPRODUCT(($A$3:$A$14=K$3)*(VLOOKUP(T(IF({1},$B$3:$B$14)),$H$3:$I$5,2,0)=K4)*$C$3:$F$14)