Multi lookup /match combo?

[J Stirling]

Hi All, this board has been a great help to me on many occasions. I have searched and checked out quite a few posts to help with this problem but am having trouble applying any of the results to my problem!

Basically our country has updated all its postcodes, our existing database of names and addresses (about 9000) all need their postal codes updated.to the new ones.

The lookup I require is up to a 3 stage one, where it needs to look at the "Street" then "Suburb" then "City" and then show the appropriate postcode for the match.

The Main Sheet is your standard "Name/Address" type layout with Column E as "Street", Column F as "Suburb" and column G as "City":

E F G H
1 Street Suburb City Postcode
2 Main Road Northcote Auckland [FROM LOOKUP]

My second Sheet "Postcodes" has all the required info for the Street/Suburb/City match:
A B C D
1 Street Suburb City Postcode
2 Main Road Northcote Auckland 3141
3 Main Road Greerton Tauranga 6882

While I can easily do a single lookup, I just cant get my head around the multi-lookup. especially when there can be more than one match.
Ideally the street would be a big help but just using the Suburb/City would probably be ok.

Thanks in advance for any help given!

 

[DonkeyOte]

J Stirling, welcome to the Board!

Ah Tauranga... reminds me of my holiday.

Anyway... I'd suggest for ease you create concatenations on each sheet.

Main Sheet: formula in I2:

=E2&":"&F2&":"&G2
copy down for all rows

Post Code Sheet: formula in E2:

=A2&":"&B2&":"&C2
copy down for all rows

Now on Main Sheet you can just use the concatenation for your lookup -- using INDEX/MATCH rather than VLOOKUP as you're looking right to left rather than left to right (ie lookup criteria in Col E but value to return in Col D)

Main Sheet: formula in H2
=INDEX('Postcodes'!$D:$D,MATCH($I2,'Postcodes'!$E:$E,0),0)
copy down for all rows

HTH

Edit: Just out of curiosity -- why if Postcodes is up to date do you need Main Sheet at all ? Can you not just use PostCodes or does the latter have more entries than your Main Sheet ?

 

[J Stirling]

Thanks for the fast reply!

Worked a treat - I had been messing around eith a match but couldnt get the syntax right.

The main sheet includes all the database info, including names etc. so the new data and old data had to be merged (all the suburb/city data had to be cleaned up in the old data also)


Once again thanks for your help!