In an Excel 2010 spreadsheet the user will select the location from the drop box. I need to have the correct post code automatically populate the adjacent cell. for example in cell A1 the user selects Brisbane from the drop box and then the post code i.e. 4000 automatically populates cell B1.

I have created a list with location names in column A and the corresponding post codes in column B. I have used this list to create the locations drop box. What do I do to have the post code populate automatically?

  • this was my first call for help over the net and I am impressed and very grateful, THANKS and ALL THE BEST, Greg – GregR Sep 29 '11 at 9:05
  • Welcome to SuperUser but please delete this "Answer" (it isn't an answer to your question) - Add it as a comment if you wish. The best reward for your helpers are upvotes and accepting (tick-mark) the best answer. – RedGrittyBrick Sep 29 '11 at 9:29

You need a VLOOKUP. First, select the two columns that make up your Suburb/Postcode database. In the top left corner of the window it will have a cell reference, possibly A1. Click here and type SuburbToPostcode and press Enter. Sort this list alphabetically.

Then, in cell B1 where you want the postcode to appear, you can type:

=VLOOKUP(A1, SuburbToPostcode, 2, False)

To break this down:

A1: Find this value.
SububToPostcode: Search the table with this name.
2: Get the value from the second column.
False: Make an exact match only, don't let Excel make assumptions on whether "Brisbane E" is "Brisbane" or "Brisbane East".

| improve this answer | |
  • He wants it triggered off of a dropbox (combobox). – Lance Roberts Sep 29 '11 at 0:12
  • @Lancce, yeah, he wants a combobox for the Suburb, then the postcode autopopulated. From the looks of his question, he already has the combobox working. This answer populates the postcode. If you select a value in a combobox, cells that reference that value are going to automatically recalculate. – Hand-E-Food Sep 29 '11 at 1:08

I'll pretend you have this type of data setup to make references in this answer easy. You can have the data where ever you like as long as the references are correct (and you keep columns A & B together).

    A     B     C     D   
1| NYC | 342 |     |     |
2| LA  | 441 |     |     |
3| DC  | 779 |     |     |
  1. Select the cell(s) you want the dropdown(s) in. If you want a full column of C, select C:C by clicking the column header.
  2. Then hit Alt + A + V + V which brings up the Data tab and then the Data Validation button and to the Data Validation... option.
  3. Allow a List and select a Source of =$A$1:$A$3 or =$A:$A for an entire column. It must be a single-column source. Leave Ignore blank and In-cell dropdown checked.
  4. In every cell in column D that is next to a validate box in C you need a vlookup() to reference the C value.
    1. So in cell D1 enter =IFERROR(VLOOKUP(C2,A:B, 2, FALSE),"")
    2. Then click on D1
    3. Then click on the little black box in the lower-right corner of D1 and drag down for as far as there are validate boxes in column C.
    4. OR If you want the entire column D to fill in as each dropdown is selected: Click column D, go to Data Validation... again and Allow: Custom and select a Source: of =IFERROR(VLOOKUP(C2,A:B, 2, FALSE),"")

I'm sorry if the instructions were to explicit for you Excel proficicy level, but I wanted to make sure you could get this running, as well as anyone else how might have this problem. Hope this is what you needed!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.