Lookup function in excel

Can somebody help me with the syntax for this? Here's the idea

Sheet 1
Country.     Zone
USA.        North America
USA.        North America
Canada.   North America
Australia.   Oceanica
Peru.         South America
USA.        North America
United Kingdom.  Europe
Germany.      Europe
USA.        North America
Canada.   North America
Australia.   Oceanica

The second column called "zone" is a lookup function (not sure I'm using the right terminology) I.e. it will fill in dynamically, based on the country to the left. So if I enter United Kingdom, "Europe" will magically appear next to it.
This is a simplified example of my shipping. For purposes of postal rates, orders have to be classed into zones.

So sheet two of my workbook is just a two column sheet with a list of all 220 we ship to. And with the corresponding zone.

For example:
USA.        North America
Canada.   North America
Australia.   Oceanica
Peru.         South America
United Kingdom.  Europe
Germany.      Europe
Italy.         Europe
New Zealand.   Oceanica
Etc.

This is a very basic outline. The actual data gets more complicated and there are thousands of records.

Could anybody help me out with a sample code? Perhaps you could upload a sample excel database to this ticket.

Thank!
LVL 1
greghollAsked:
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.

TomRScottCommented:
You can find a number of examples on-line.  However, it was quick enough to put an example together.

See Attached.  Note, I also put in the country "UK". Once folks start putting data into your client database, you can bet that United Kingdom will sometimes be entered as UK. Similar double/triple/... entries are recommended.

Also note, that the Zone_Table (a named range on the Zone Table tab) should be sorted.

However, if this database gets more than a hundred or so records, I recommend setting up a real database. Spreadsheets/workbooks are great for designing databases and maintaining smaller databases but larger data stores easily get out of hand. More importantly good database software has a commit/rollback feature to facilitate fixing/avoiding mass deletions from the database.

Tom
Client-Country-Zone-Lookup-Examp.xlsx
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
Brian PiercePhotographerCommented:
I've taken the liberty of amending #TomRScott's solution a bit by removing the #N/A where you haven't entered a country yet and adding  list to that gives a drop down box for selecting the countries - this forces people to choose from the list and stops spelling errors and the situation where some people may type UK, others Britain and others United Kingdom etc.
Client-Country-Zone-Lookup-Examp.xlsx
0
Rob HensonFinance AnalystCommented:
When using VLOOKUP the source data does not have to be sorted.

The vlookup has four parameters:

Lookup value - the value to find
Lookup range - the range in which to find it
Offset column - the column in the lookup range to return
Lookup type - True or False,

Using False as lookup type will find an exact match no matter what sort order, whereas True will find the nearest close match depending on sort.

Thanks
Rob H
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Brian PiercePhotographerCommented:
VLOOKUP DOES require data to be sorted !
0
Rob HensonFinance AnalystCommented:
Only if you are not using the false parameter to find an exact match.
0
Brian PiercePhotographerCommented:
If you use the true parameter, then unless the items are in order it will give the wrong answer take the following example

Suppose you want to give a discount based on sales according to the following scale
Sales    discount
100       0%
200       5%
500       8%
1000    10%

if someone sells 250 worth, then id they are in this order, Excel will look down the column, it will go past 200 and when it hits 500 it will realise its gone past and return the previous value ie 5% which would be correct.


If the list not in order eg:
100       0%
1000     10%
500       8%
200       5%

excel will look down the column when it reaches 1000 (the second entry) it wiil think its gone past 250 and return the value 0 - which would be wrong

I've attached an example that shows what I mean.
Vlookup.xlsx
0
Rob HensonFinance AnalystCommented:
Correct if you use the TRUE parameter. Sort order does not matter when using the FALSE parameter.
0
greghollAuthor Commented:
I thank everybody for the comments. I´m examining them. At the same time, we don´t need to overthink the entire issue.

This for an in-house shipping manifest. There is no risk of 3rd parties entering in wrong data.

The order system dumps the addresses in a list. The country names will alway be standarized.

From there we are simply needing the shipping manifest to list which postal zone each country is in for the purposes of figuring out the shipping charge.

Thanks
0
greghollAuthor Commented:
Awesome. This exactly what I need.

Greg
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 Office

From novice to tech pro — start learning today.