Reputation: 103
I have a problem when trying to do index matching. I want to match the item and date from table 1 and table 2 to obtain the Order balance. I really have no idea how to solve this.
Here is my data, Table 1:
ITEM ITEM2 QUANTITY 29/09/2018 04/10/2018 05/10/2018 06/10/2018 08/10/2018 09/10/2018
A A1 0.0022
A A2 0.0004
A A3 17
B B1 2
A A4 16
A A5 2
Table 2
Item DATE OrderBalance
B 29/09/2018 11
A 29/09/2018 5
B 04/10/2018 2
B 05/10/2018 7
B 06/10/2018 11
A 06/10/2018 1
B 08/10/2018 3
B 09/10/2018 19
Here is the expected output.
ITEM ITEM2 QUANTITY 29/09/2018 04/10/2018 05/10/2018 06/10/2018 08/10/2018 09/10/2018
A A1 0.0022 5
A A2 0.0004 5
A A3 17 5
B B1 2 11 2 7 11 3 19
A A4 16 5
A A5 2 5
Upvotes: 1
Views: 39
Reputation:
The first column can be returned with a SUMIFS.
=SUMIFS(Table2[OrderBalance], Table2[Item], [@ITEM], Table2[DATE], Table1[[#Headers],[09/29/18]])
You can modify the formula for adjacent columns.
Upvotes: 1