Ethnicity - Match code to a word in Excel

[spacebar]

Hi All,

 

I know it’s probably easy, but I have been asked to collect all the data we hold on staff and their ethnicity, age, etc. and dump it into a spreadsheet.

I have managed to get all of the information out of our 15 year old database system, but it records ethnicity as a code, rather than an actual word. I know this is quite common in these systems.

 

I know what code stands for what ethnicity, 1=white British for example, But does anyone know how to make Excel match up a number with a word and produce the result in a new cell?

 

It would save me a lot of time and I would really appreciate if someone could help on this.

 

Thanks.

[LosOjos]

You'll want to use a vlookup.

 

Create a table with the code in one column and the description immediately to the right, then perform a vlookup against it.

[bladedanny]

A vlookup would probably be the best. On another sheet (or separate part of the current sheet) Set up a two column table. In the first column have the code and in the second have the text to display.

 

Then in your main table create a new column next to the ethnicity code and put it.

=VLOOKUP($CELLWITHCODE,$RANGEOFTABLE,2), eg =VLOOKUP(A6,$E$12:$F$21,2) The '2' is the column in which to get the text from.

 

Hope this helps.

 

--edit--

 

Make sure that in the $RANGEOFTABLE part of the vlookup your range has the $ signs as above, otherwise it will change as you replicate it down.

Edited November 7, 2013 by bladedanny

[spacebar]

Thanks bladedanny and losojos, I managed to do it and got there in the end.

 

Thank-you.