How do I join two tables in Excel?

Question

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?&nbsp;Table 1Warehouse&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Product Category&nbsp; &nbsp; &nbsp;Product NameWarehouse 1&nbsp; &nbsp; &nbsp; &nbsp;Fruits&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AppleWarehouse 1&nbsp; &nbsp; &nbsp; &nbsp;Fruits&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;BananaWarehouse 2&nbsp; &nbsp; &nbsp; &nbsp;Diary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Milk&nbsp;Table 2Product Category&nbsp; &nbsp; &nbsp;Product Name&nbsp; &nbsp;QuantityFruits&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Apple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Fruits&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Banana&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6Diary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7&nbsp;Table 3Warehouse&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Product Category&nbsp; &nbsp; &nbsp;Product Name&nbsp; &nbsp; QuantityWarehouse 1&nbsp; &nbsp; &nbsp; &nbsp;Fruits&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Apple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1Warehouse 1&nbsp; &nbsp; &nbsp; &nbsp;Fruits&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Banana&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 6Warehouse 2&nbsp; &nbsp; &nbsp; &nbsp;Diary&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Milk&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7&nbsp;

ecovonrein · Answer

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.)

lorenzo · Answer

waterding&nbsp;&nbsp;&nbsp;in&nbsp;M4:=SUMIFS(Table2[Quantity],
        Table2[Product Category],[@[Product Category]],
        Table2[Product Name],[@[Product Name]]
)&nbsp;

lorenzo · Answer

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

Forum Discussion

How do I join two tables in Excel?

3 Replies

Resources