Using excel to create freight comparison

Hi,

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.

Regards,

Dave
Australian-Postcodes.csv
Carrier-Comparison-Nationally.xls
dmh01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

wolfcamelCommented:
you will need to use the vlookup or command.
0
byundtMechanical EngineerCommented:
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
0
byundtMechanical EngineerCommented:
I'd have suggested a formula for the freight rate, but column D is blank in the Australian-Postcodes.csv workbook that you posted.
0
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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.
Carrier-Comparison-Nationally.xls
0
byundtMechanical EngineerCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
byundtMechanical EngineerCommented:
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.
0
dmh01Author Commented:
I also changed the look up to find the post code once the suburb has been entered.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.