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