Using excel to create freight comparison


I am using Excel 2007 and I am trying to put together a freight comparison chart and need to add the postcodes for a state so that when I enter the postcode in a cell the suburb, state and freight rate appears in their own cells.

Is this possible?

I am a bit of a noob to playing with Excel and have all these ideas, just not sure how to implement them.

I have attached a copy of the spreadsheets to work with and would appreciate any help. The postcode will be entered in column 'D' and the Suburb and state will be in 'B & C' The rates are in Column 'I'. The rates will be taken from Column D' in the Australian Postcodes  sheet.


Who is Participating?
byundtConnect With a Mentor Commented:
You were not putting the data or formulas in the right place

The postcode was supposed to go into cell D89. The formula was supposed to go in cells B89 and C89:
=VLOOKUP($D89,'Australian-Postcodes.xlsx'!$A$1:$C$65536,2,FALSE)           returns suburb in B89
=VLOOKUP($D89,'Australian-Postcodes.xlsx'!$A$1:$C$65536,3,FALSE)           returns state in C89

I notice that you changed the postcode workbook from a .csv to a .xlsx, so I changed the formula accordingly.

Since the Carrier Comparison file is .xls file format, it cannot refer to rows beyond 65536. If you would save the Carrier Comparison file as .xlsx, then your formula could reference entire columns:
=VLOOKUP($D89,'Australian-Postcodes.xlsx'!$A:$C,2,FALSE)           returns suburb in B89
=VLOOKUP($D89,'Australian-Postcodes.xlsx'!$A:$C,3,FALSE)           returns state in C89
you will need to use the vlookup or command.
As wolfcamel suggested, you can use VLOOKUP to return the suburb and State. On the Ex. Carrum Downs, VIC worksheet, the following formulas will work:
=VLOOKUP(D3,'Australian-Postcodes.csv'!$A$1:$C$65536,2,FALSE)              returns suburb
=VLOOKUP(D3,'Australian-Postcodes.csv'!$A$1:$C$65536,3,FALSE)              returns state
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

I'd have suggested a formula for the freight rate, but column D is blank in the Australian-Postcodes.csv workbook that you posted.
dmh01Author Commented:
Hi byundt, thanks for the help.

However I need to be able to input a postcode into column 'D' and then the suburb and state are in B and C.

The VLOOKUP you sent me doesn't seem to do this. AM I doing something wrong here? I copy the code and put it into cell D and it shows Laverton North, not postcode 3026. See attached.
I should also mention that your postcode workbook includes more than one suburb for many of the postcodes. The VLOOKUP formula will only ever return the first suburb.

For example, a VLOOKUP formula for postcode 3025 will always return ALTONA EAST, and never ALTONA GATE or ALTONA NORTH.
dmh01Author Commented:
I also changed the look up to find the post code once the suburb has been entered.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.