Multi lookup /match combo?

J Stirling

New Member
Joined
Jul 21, 2008
Messages
3
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!
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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 ?
 
Last edited:

J Stirling

New Member
Joined
Jul 21, 2008
Messages
3
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,177
Messages
5,623,199
Members
415,957
Latest member
Newguy1924

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top