Conditional IF Question

[vivekd5]

Hi

I have a few markets I want to categorize and from the list below, I want to categorize as below:

THOMPSON REGION, BC, CAN
GALICIA, SPAIN
REGIONAL GERMANY NORTH RHINE-WESTPHALIA
JUTLAND, DENMARK
PADOVA (PROVINCE), ITALY
HOKKAIDO, JAPAN
BUSSELTON, WA, AUSTRALIA
MELBOURNE, VIC, AUSTRALIA
HUNTER VALLEY, NSW, AUSTRALIA

If text contains:

Australia -> ANZ
Japan -> Asia
Denmark -> Europe etc

Is there a way I can do this with IF statement? I want to avoid using vLookup for this.

 

[Hui]

The issue with using an If() function is that after 4 or 5 countries the formulas become too long and hence prone for errors

Using a Lookup has the advantages of simplicity as well as flexibility in adding countries as required

 

[vivekd5]

Hi Hui

Thanks for your reply. Got it.

Just as a followup to this question - let's say, for simplicity, that I want to mark Australia / New Zealand as ANZ and rest as "Others". How could I write the formula statement?

Vivek

 

[Wolfgang]

vive,
You can use * to help as a wildcard.
So it would be:
=vlookup(A1, B1:C99, 2,false)
Data as follows:
A1 --> *Australia*
B1~B99 --> BUSSELTON, WA, AUSTRALIA (etc..)
C1~C99 --> ANZ, Asia, Other, etc

I hope that helps

 

[vivekd5]

Thanks for your replies Hui and Wolfgang!

 

[Nebu]

Hi:

You can use the following method, formula in colored cells.

Thanks