Forum Discussion

waterding's avatar
waterding
Copper Contributor
Jul 12, 2022

How do I use formulas/functions to match two tables with two fields?

Using Product Category and Product Name, I can match the records between Table 1 and Table 2. How do I use Excel formulas/functions to return the corresponding Warehouse of each record in Table 1?

 

Table 1

Product Category     Product Name       Warehouse

Fruits                           Apple                      ?

Fruits                           Banana                    ?

Diary                           Milk                         ?

 

 

Table 2:

Warehouse         Product Category     Product Name

Warehouse 1      Fruits                        Apple

Warehouse 1      Fruits                        Banana

Warehouse 2      Diary                        Milk

 

 

 

Formulas and Functions
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jul 12, 2022

    waterding  There are a couple options including FILTER() but here is a more simple approach:

     

    =XLOOKUP( F3:F5 & G3:G5,   B3:B5 & C3:C5,  A3:A5,  "")

    or using table references:

    =XLOOKUP([@[Product Category]] & [@[Product Name]],
         Table1[ Product Category] & Table1[Product Name],
         Table1[Warehouse], 
         "")

     

    basically concatenate the 2 columns and do the search, but the terms need to be the same (and no extra spaces)  see attached

Resources