Suburb and Postcode List

**crania** · 11-06-2007, 08:54 AM

Hi Guys,

I'm really ordinary at excel so be nice!!

I have a spreadsheet that has a list of all suburbs in my state in column 1 with the corresponding postcodes in column 2. In column 3 I have organised the suburb names into regions that i got from a different list. Needless to say the suburbs/postcodes are not aligned. What I want is a formula for moving/copying the postcodes to a 4th column next to my new organisation of suburbs. I've tried IF functions but being quite hopeless I've had no luck. Basically I need to formula to say something like Column 4 = Column 2 when Column 3 = Column 1. Since they dont line up I need to search Column 1 until it does equal column 3. I want no blanks. Is this even possible?? Thanks in advance.

Crania

**daddylonglegs** · 11-06-2007, 08:56 AM

Assuming your data is in columns A, B and C use a VLOOKUP formula in column D, e.g. in D1

=VLOOKUP(C1,A:B,2,0)

copied down the column

**NBVC** · 11-06-2007, 08:57 AM

Probably a Vlookup() type formula will work..

=Vlookup(C2,A:B,2,0) copied down.

where C2 is the first suburb in column 3 and columns A and B contain your lookup list.

**crania** · 11-06-2007, 09:06 AM

I love forums, so many experts. Thanks so very very much guys. Can I ask another quick, probably really simple, question. If I now delete columns 1 and 2 will I lose all the data in column 4? Is there a way to stop this? Thanks again

**NBVC** · 11-06-2007, 09:08 AM

Originally Posted by crania

I love forums, so many experts. Thanks so very very much guys. Can I ask another quick, probably really simple, question. If I now delete columns 1 and 2 will I lose all the data in column 4? Is there a way to stop this? Thanks again

You can copy column 4 and then click D1 (first cell in column 4) and go to Edit| Paste Special and select Values...click OK.

Then you can delete columns A and B