How do I join two tables in Excel?

New Contributor

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


3 Replies
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):


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





in M4:

        Table2[Product Category],[@[Product Category]],
        Table2[Product Name],[@[Product Name]]



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