Populating Field in Table1 Cell Based on Certain Table1 Cells matching Table2 Cells

[jimdegeorge]

Hi

I have two tables (Table1 and Table2) on 2 different tabs (Sheet1 and Sheet2, respectively).

Table1

Name	City	Line	District
James	Boston	Hard Goods
John	Los Angeles	Soft Goods
William	Baltimore	Soft Goods
Harold	Miami	Hard Goods

<tbody>
</tbody>

Table2

Name	City	District
James	Boston	North East
John	Los Angeles	West
William	Baltimore	Central East
Harold	Miami	South East

<tbody>
</tbody>

When NAME and CITY from Table1 are found on Table2, I want to save Table2's DISTRICT value in the appropriate Table1 DISTRICT cell.


This is the desired Table1 result:

Name	City	Line	District
James	Boston	Hard Goods	North East
John	Los Angeles	Soft Goods	West
William	Baltimore	Soft Goods	Central
Harold	Miami	Hard Goods	South East

<tbody>
</tbody>

I've tried these formulas in Table1 DISTRICT...

=IF(Table1[@[Name]:[City]]=Table2[@[Name]:[City]],Table2[@District],""), but this returns #VALUE !

=If(VLOOKUP(Table1[@[Name]:[City]],Table2[[Name]:[City]],C,FALSE),Table2[@District],""), but I can't get it formatted correctly.


I'd really appreciate any help!
Thanks!
Jim

 

[Fazza]

Hi, Jim,

What about a concatenation of the Name & City fields and then use that for INDEX/MATCH or VLOOKUP?

Or an UPDATE query, using

UPDATE Table1 T1 INNER JOIN Table2 T2 ON T1.Name = T2.Name AND T1.City = T2.City
SET T1.District = T2.District

regards