Best Method to Re-Categorize Categorical Data in Excel

Hey Team,

I am curious as to what function would be best to re-categorize Regions in an Excel Table so PowerBI (Bing Maps) will recognize it by region.

I need to convert the following:

If EMEA then = Europe
If AP then = Asia
If LAR then = South America
If NAR then North America
If Other then = Other

regionname.PNG



Any help would be appreciated!

You already have the basis of a lookup table with your series of If sentences - enter the values that you currently have in a column somewhere and against them in the next column enter what you want them to become, like this:

EMEA ……. Europe
AP ……….. Asia
LAR ………. South America
NAR ………. North America
Other ……. Other

Suppose you do this in columns X and Y. Then you could use a formula along these lines:

=VLOOKUP(region,X:Y,2,0)

where region is the cell reference for the first item that you want to change. Then you can just copy the formula down as far as you need to.

Hope this helps.

Pete

Thank you!!!

SO what if I have one column that collects sample data with the words A, B, and C.

A
B
C

and I want to create a second column that looks at A,B, and C and puts the actual word.

Apple
Banana
Cookie

Is this time for an "IF" function?

Hey Glenn - for the "table_array" it looks like you can't pull from a second sheet or tab. Is this true? If so - I cannot use this. I am trying to automatically convert the A..B..C...to Apple...Banana...Cookie in a database.

NEVERMIND! Thank you Glenn and Pete! I got it. You all are great. Thank you.