Power Pivot Lookup Duplicate Values

[prihle]

Hi,
I am using Excel 2010 with PowerPivot add-in, very much a newbie in Power Pivots.
I have a set of shipment data compiled from various sources. They have to be cleaned and grouped into lanes.
For the purpose of cleaning the origin and destination points I have a list of all variations of locations (country, city, zip code) as it exists in the source data (table Locations).

Task:
I need to assign correct zip codes to the cities in the Locations table using a zip code database for Europe stored in Power Pivots (table GEO-Europe).
So, I have two tables in the Power Pivots with the following relevant columns (named same in both tables): Country (cntry codes), PostalCode, Locality (containing the city).
1. Locations table – where I need to fill in the zip codes
2. GEO-Europe table – table to lookup the zip codes

I need a solution that would filter the GEO-Europe table and return a PostalCode based on matching Country and City in the Locations table (the filter will most likely return multiple rows, I`m happy if it can return any PostalCode matching the above criteria)

I have actually already done a big part of it on standard Excel sheet (breaking up the zip database into files by country) but it was not very efficient (too complex and time consuming). If it could help give you a better picture I used the following formula:
=VLOOKUP(G2,OFFSET(WE1_CityOffset,MATCH(H2,WE1_CntryCount,0)-1,0,COUNTIF(WE1_CntryCount,H2),2),2,FALSE)
G2 – Locality (Locations)
H2 – Country (Locations)
WE1_CityOffset – Locality (GEO-Europe $O$1)
WE1_CntryCount – Country (GEO-Europe)
Note: Same layout as the tables in the attached file

I wrote this post assuming that I would attach sample data but I cannot see where to upload it. Hopefully my description will be clear enough even without the sample.

Thanks in advance. I appreciate any help you can offer.
Regards,
Lenka

 

[scottsen]

Actually I think a sample would totally help Drop one on OneDrive,GoogleDrive,DropBox,etc?

 

[prihle]

Did not really think of that . Here you go https://drive.google.com/open?id=0B4e3QEx4BooMNjBHVnpDUFp2cUE&authuser=0

If it was any help here is how I did it with Excel formulas https://drive.google.com/open?id=0B4e3QEx4BooMdlRKdTE5WG1IQ0E&authuser=0

Thanks a lot!

 

[scottsen]

well, I can give you a formula that works, but I can't help w/ the quality of your data...

I added a calc column to the Locations table, which looked up values in GEO-Europe but the majority of cities were not there...

=CALCULATE(FIRSTNONBLANK('GEO-Europe'[PostalCode], 1), FILTER('GEO-Europe', 'GEO-Europe'[Country] = Locations[Country] && 'GEO-Europe'[Locality] = Locations[Locality]))