How do I join two tables in Excel?

[waterding]

I have Table 1 and Table 2. How do I use formulas/pivot table to match Product Category and Product Name between Table 1 and Table 2, and come up Table 3?

Table 1

Warehouse         Product Category     Product Name

Warehouse 1       Fruits                           Apple

Warehouse 1       Fruits                           Banana

Warehouse 2       Diary                           Milk

Table 2

Product Category     Product Name   Quantity

Fruits                           Apple                      1

Fruits                           Banana                    6

Diary                           Milk                         7

Table 3

Warehouse         Product Category     Product Name    Quantity

Warehouse 1       Fruits                           Apple                          1

Warehouse 1       Fruits                           Banana                        6

Warehouse 2       Diary                           Milk                             7

[#2]

To make Table 3, you are just adding the Warehouse to Table 2. So insert a column before Table 2 (I call it A, such that Product Name is in C) and before Fruits/Apple write (conceptually):

=XLOOKUP(Table2[ProductName];Table1[ProductName];Table1[Warehouse])

That should populate the entire Warehouse column. (Product Category does not seem to inform your mapping.)

[#3]

@waterding 

in M4:

=SUMIFS(Table2[Quantity],
        Table2[Product Category],[@[Product Category]],
        Table2[Product Name],[@[Product Name]]
)

[#4]

@waterding 

Alternatively, if Table1 contains unique [Product Name] as you exposed + you run a recent version of Excel (>= 2013) on Windows you can do it with Power Pivot