Apis
Apis

Reputation: 103

Excel index matching 2 table

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

Answers (1)

user4039065
user4039065

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.

enter image description here

Upvotes: 1

Related Questions