IF, search some text with OR function??

[excesspotential]

I have countries listed in a column I want to search the column for Japan, China, Brazil, and apply the region of "Asia" or South America"

This is what I have so far but I am thinking and i can clean it up with "OR" function but I can figure out the order.

=IF(ISNUMBER(SEARCH("Belgium",C2:C99)),"Europe",IF(ISNUMBER(SEARCH("Auckland",C2:C99)),"Asia",IF(ISNUMBER(SEARCH("Germany",C2:C99)),"Europe",IF(ISNUMBER(SEARCH("Arg",C2:C99)),"South America",IF(ISNUMBER(SEARCH("Australia",C2:C99)),"Asia",)))))

Any Recommendations?

[Tony Valko]

If you have "many" countries how about creating a table and using a lookup function?

http://contextures.com/xlFunctions02.html

[excesspotential]

Yes sir, I am trying to apply the region of each into a new cell. so the function looks for the country then applies the region. Then I create a pivot table to make my pie chart to tell me what percentage of shipments go to each region.

[excesspotential]

Okay I like that VLOOKUP...

[FDibbins]

So what did you come up with?

[Tony Valko]

Good deal. Thanks for the feedback!

[excesspotential]

So I created a pivot table for the all the countries which consolidated them in one table. The manually assigned the region in a separate column. One my main worksheet I used the VLOOKUP for all the countries and used the pivot table to assign the region. Probably a more efficient way but I just learned how to use Vlookup!