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
[TABLE="width: 285"]
<tbody>[TR]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Line[/TD]
[TD]District[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Boston[/TD]
[TD]Hard Goods[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Los Angeles[/TD]
[TD]Soft Goods[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]William[/TD]
[TD]Baltimore[/TD]
[TD]Soft Goods[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Harold[/TD]
[TD]Miami[/TD]
[TD]Hard Goods[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Table2
[TABLE="width: 220"]
<tbody>[TR]
[TD]Name[/TD]
[TD]City[/TD]
[TD]District[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Boston[/TD]
[TD]North East[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Los Angeles[/TD]
[TD]West[/TD]
[/TR]
[TR]
[TD]William[/TD]
[TD]Baltimore[/TD]
[TD]Central East[/TD]
[/TR]
[TR]
[TD]Harold[/TD]
[TD]Miami[/TD]
[TD]South East[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 285"]
<tbody>[TR]
[TD]Name[/TD]
[TD]City[/TD]
[TD]Line[/TD]
[TD]District[/TD]
[/TR]
[TR]
[TD]James[/TD]
[TD]Boston[/TD]
[TD]Hard Goods[/TD]
[TD]North East[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Los Angeles[/TD]
[TD]Soft Goods[/TD]
[TD]West[/TD]
[/TR]
[TR]
[TD]William[/TD]
[TD]Baltimore[/TD]
[TD]Soft Goods[/TD]
[TD]Central[/TD]
[/TR]
[TR]
[TD]Harold[/TD]
[TD]Miami[/TD]
[TD]Hard Goods[/TD]
[TD]South East[/TD]
[/TR]
</tbody>[/TABLE]


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