Help with replacing values.

**igolfniray** · 04-13-2012, 02:47 PM

I have a list of text values each have a category number i.e

Col1 || Col2
1508 || John Smith
1508 || Jane Doe
1509 || John Doe
1509 || Jane Smith
1510 || Robert Smith

And another two columns relating the category number i.e.

Col1 || Col2
1508 || Solicitors
1509 || Teachers
1510 || Drivers

I have 30,000 names and 1500 categories.

I need my sheet to end up like this:

Col1 || Col2
Solicitors || John Smith
Teachers || John Doe

So I need to search i.e "1508" and replace it with the text associated with it.

I know it can be done with search>replace all but I have 1500 categories... I need something automatic!

Can anyone help me with this?

Thanks in advance!

Ray

**DGagnon** · 04-13-2012, 02:54 PM

the best way to do this would be by putting a vlookup formula in Col"3" then after it finds all of the values to paste it over the Col1 data

=VLOOKUP(A1,E:F,2,0)

where A contains your first table with numbers, B the names, and range E:F has your second table with the numbers and categories.

**FDibbins** · 04-13-2012, 02:58 PM

it would be easier to help if you uploaded a sample file, but if i understand your layout, a vlookup() will do what you want. Unsure tho if the below is in the same cell, or 2 different cells
Col2 || Col2
1508 || Mr. John Smith

assuming they are not, and A2=1508 and "name" = B2 (sheet1)....1508=A2, "category"=B2 (sheet2), where you want your answer, try...
=vlookup(sheet1!A2,sheet2!A2:B30000,2,false)

let me know how this works for you