HELP

[ISAF Media Analysis]

I need a formula that would allow me to enter a the names of multiple
countries into a column and have it automatically placed into another column
by continent. For example:

If: Then:

A1 is United States B1 should be North America
A2 is Mexico B2 should be North America
A3 is France B3 should be Europe
A4 is Germany B4 should be Europe
A5 is Japan B5 should be Asia

So, if A1:A5 is United States, then B1:B5 should automatically be North
America.

If there is anyone who can help me, it would be greatly apprecieted. Thanks
in advance

 

[Mike H]

Hi,

Somewher out of the way build yourself a table looking like below. I put
mine in F1 - G5

United States North America
Mexico North America
France Europe
Germany Europe
Japan Asia


In B1 enter the formula
=IF(A1="","",IF(ISNA(VLOOKUP(A1,F1:G5,2,FALSE)),"Lookup Not
found",VLOOKUP(A1,F1:G5,2,FALSE)))

Drag down as far as you need.
If your table is larger then alter the range in the formula to match the table

Mike

 

[Mike H]

Forgot the absolute reference, use this instead

=IF(A1="","",IF(ISNA(VLOOKUP(A1,$F$1:$G$5,2,FALSE)),"Lookup Not
found",VLOOKUP(A1,$F$1:$G$5,2,FALSE)))

Mike

 

[Gary''s Student]

First create a master list in cols G & H:

United States North America
Canada North America
Mexico North America
England Europe
German Europe
France Europe
Spain Europe
Japan Asia
China Asia
Grand Duchy of Fenwick Europe

and then in B1:

=IF(A1="","",VLOOKUP(A1,G:H,2)) and copy down

You can put the master where you like.