Index and match function for same item in the table

Dear all,

I have a excel sheet with table. For actual file, it has more than 300 account manager's sales data.

I use index and match formula function, therefore, I don't need to use find and search.
However, for certain account manager, they change from team 1 to team 2. My boss would like to combine two data of same account manager.
How can I modify the following formula to complete tasks?

=INDEX($C4:$D9999,MATCH($B17,$A$4:$A$999,0),MATCH($D$16,$C$3:$D$3,0)

C4 is account manager, David.

I have attached the excel file for reference, thanks

Using your sample file:

Please Login or Register  to view this content.

Alternatively push your source data into a Pivot Table.

Thanks a lot.

However, may I ask how can I integrate the sumif function with index and match?
There is limition of sumif function. If i use sumif, the column (e.g. column D, loan) should be fixed. As i changed column D (from loan to new product), i need to change the formula . If using index and match, it is much convience

C17:
=SUMIF($A$4:$A$9,$B17,C$4:C$19)
copied to C17:D19

You will note the use of relative references in the formula suggested [C$4:C$19]. As the formula is copied to the right so the summation range will adapt from C to D etceteras.

If you really do need to use an INDEX/MATCH (to accommodate the fact that columns change on an ad hoc basis) you can:

Please Login or Register  to view this content.

Use of C:Z in the above is hypothetical - modify as necessary.