Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel Formulas

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Excel Formulas

· Low Priority · Version 2010

Reply Tue 10 Jan 2012, 14:04Delegate Lisa said...

Lisa has attended:
Excel Introduction course

Excel Formulas

Hello,
I have an Excel spreadsheet with 2 tabs, 1st tab has a column with country names and their respective zones, i.e. Europe, Asia, etc in the 2nd column.
The 2nd tab has a list of countires, is there a formula I can use to automatically enter the correct countyr zone on the 2nd tab?

Many thanks

For upcoming training course dates see: Pricing & availability

Reply Wed 11 Jan 2012, 10:06Trainer Anthony said...

RE: Excel Formulas

Hi Lisa, thanks for your query. You'll need to use the VLOOKUP function to achieve this, which is something we cover on our advanced course. Have a look here for a quick walkthrough:

http://www.bettersolutions.com/excel/EDH113/YI519060881.htm

Hope this helps,

Anthony

Reply Wed 11 Jan 2012, 10:27Trainer Doug said...

RE: Excel Formulas

Hi Lisa

Here's how to do what you asked with the country zones.
Suppose your Sheet1 looks like this
COUNTRY ZONE
UK Europe
France Europe
India Asia
Japan Asia
SA Africa

and Sheet2 looks like this
COUNTRY ZONE
Japan ?
UK

Type this Vlookup formula next to Japan in Sheet2 to show the correct zone.
=VLOOKUP(A2,Sheet1!$A$2:$B$6,2,FALSE)

The notes Anotny sent expain how the formula works which is all covered on our Excel Advanced course.
Let us know if it worked!

Regards
Doug

Reply Wed 11 Jan 2012, 12:07Delegate Lisa said...

RE: Excel Formulas

Dear Anthony and Doug,

Thank you for getting back to me.
I've been trying VLOOKUP formula yesterday to match regions to my country names, only I was referring to a range on sheet 1 in my formula rather than the actual sheet.
Doug, I've just tried your formula, but I am only getting N/A results.
I've tried this both in Excel and CSV formats.

Regards,

Lisa

Reply Mon 23 Jan 2012, 09:53Trainer Doug said...

RE: Excel Formulas

Hi Lisa

Maybe easiest for you to email your example and I can get back to you with why it says N/A. Send to
dougdunn56@gmail.com

Regards
Doug

Doug Dunn
Best STL

Excel articles

· What You Need To Know About LOOKUP Functions In Excel 2007/2010
· Why Printing Needn't Be A Grey Area In Excel
· Find Out Your Age In Days With Excel
· Creating Smarter, Customized Invoices for your Small Business with Microsoft Excel
· How To Create A Data Table In Excel 2010

Please browse our web site to find out more about
microsoft excel 2003 vba courses and other Microsoft training courses.

Excel tip:

New to Excel 2010 - Sparklines!

Excel 2010 includes a new feature called Sparklines which are tiny charts that fit into a single cell and plot data in cells from the worksheet. There are a host of formatting and styles that can be applied to them and they are really quite interesting.

>insert
>sparklines
>Choose any style you want

You will be asked for the range and it will automatically select the cell your in to insert the sparklines.

View all Excel hints and tips