Help with a formula

[Dave]

Hi,

I am trying to create a fairly simple formula where when the use
enters a 3 digit country code such as CAN, excel automatically enters
the part of the world that code is from. So basically I have 7
categories (South America, North America, Asia Pacific, South Pacific,
Europe, Middle East and Africa with each having the relevent 3 digit
codes assigned to them for example North America = USA, YYZ, RDU - the
most has 25 codes) so when USA is entered, the following cell would
enter South America.

So two things I guess I need to do :-

1) Assign country codes to country regions
2) Use a formula to enter correct country region dependent upon 3
digit country code.

Can someone assist me with this as I have now spent quite a bit of
time looking through Excels help, on the web etc.

Many thanks.

 

[Leo Heuser]

Hi Dave

One way:

Make a 2-column list e.g. in L2:M100,
L2:L100 containing the country codes
and M2:M100 containing the names.

In e.g. D2 enter the country code.
In e.g. E2 enter this formula:

=VLOOKUP(D2,L2:M100,2,False)

 

[Max]

Here's one way using OFFSET and MATCH:

Steps
-------
a. Set-up your table in say, Sheet1
in cols A & B, row1 downwards, viz:

North America USA
North America YYZ
North America RDU
South America VEN
South America BRA
South America CHI
etc

b. In Sheet2

Assume col A will be used for input
of the country codes, from A2 down

Put in say, B2:
=OFFSET(Sheet1!$A$1,MATCH(TRIM(A2),Sheet1!B:B,0)-1,0)

Copy down col B

Col B will return the country corresponding to the codes in col A

TRIM() is used to make the matching of the codes more robust,
just in case users inadvertently enter any leading or trailing spaces
(with the spacebar) in their typing

What-if invalid codes are entered ?
-----------------------------------------
To alert users should they happen to enter any invalid codes,
you can use an IF(ISNA(...),< Alert Message >,(...)) construct, i.e.:

Put instead, in B2:
: =IF(ISNA(OFFSET(Sheet1!$A$1,MATCH(TRIM(A2),Sheet1!B:B,0)-1,0)),"Invalid
code. Check &
Re-enter",OFFSET(Sheet1!$A$1,MATCH(TRIM(A2),Sheet1!B:B,0)-1,0))

 

[Peter Atherton]

Dave

VLookUP is what you want
If this is the table of values in H1 to I2
UEM united Emerates
usa Unitited States

Then the fomula =VLOOKUP(H5,H1:I2,2,1)will produce this
result

uem united Emerates

Note the list has to be in Ascending order for this
formula to work correctly.

See if this is what you want and post back if not.

Regards
Peter