Excel - Vlookup - multiple conditions - textual data

[rosieribbons]

Hi Everybody.

Was wondering if anyone knew how to do the following (apologies if my excel jargon is off, I'm not what you'd call a pro!)

OK so my situation is this. I'm working with a database that holds information on individuals backgrounds. At the moment I'm looking at education history. I have pulled off a list which details what the database currently says each individual has specified as their degree, but they are all worded slightly differently, and I need to introduce some consistency into the wording to allow us to search and sort the data better.
So I've got sheet1, which has a list of all the unique ways a degree is listed within the database, and an adjacent column which specifies what I want to change it to.
And in sheet2 I have a list of serial numbers for the individual records, and adjacent to that, a column detailing the unique degree listings.

So I need to find a way to tell excel "when you find X in column 2 of sheet 2, change it to Y from sheet 1 column 2, but if you don't find X don't do anything. And if you find Z in column 2 of sheet 2, change it to F from sheet 1 column 2, but if you don't find Z don't do anything".....

BUT I have about 2,500 different conditions that I need to apply at the same time.

Does that make sense? and can anyone suggest a way to achieve this? a friend suggested Vlookup but I can't seem to find any evidence that Vlookup can apply such a large volume of conditions simultaneously. Also all the data I'm working with is textual rather than numerical and I can't find much on how to make that work. I have 20,000 records to amend, I really don't want to do them one at a time!

thanks,

Rosie

 

[Robert Mika]

thanks for your help! I'm gonna give it a whirl now...wish me luck!

Just to add that if you are working on excel version prior to 07 IFERROR will not work:
Used instead:
= IF(ISNA(VLOOKUP(CurrentCourseNameColumn, YourLookupTableRange, 2, FALSE)), CurrentCourseNameColumn,VLOOKUP(CurrentCourseNameColumn, YourLookupTableRange, 2, FALSE))

 

[rosieribbons]

Oh dear.... not working. Not even giving me any error messages. No dreaded #N/A. Nothing. Just a lonely old formula not doing anything.



if anyone has any ideas.... this is what I've tried so far

= IFERROR(VLOOKUP(C, $F$4:$G$2391, 2, FALSE), C))
=VLOOKUP(C,$F$4:$G$2391,2,FALSE),C))

various other combinations with and without spaces, capitals, etc.

I'm using excel 2010

 

[Robert Mika]

You have to replace the "C" with reference to a cell like this
=IFERROR(VLOOKUP(A2,$F$4:$G$2391,2,FALSE),A2)

 

[rosieribbons]

You have to replace the "C" with reference to a cell like this
=IFERROR(VLOOKUP(A2,$F$4:$G$2391,2,FALSE),A2)

ahh ok,

so if I want to apply the formula to a whole column would I have to put something along the lines of C2:C20929 (or $c$2:$c$20929) or can it only be one cell at a time?

 

[Robert Mika]

ahh ok,

so if I want to apply the formula to a whole column would I have to put something along the lines of C2:C20929 (or $c$2:$c$20929) or can it only be one cell at a time?

Drag/copy the formula down.

 

[rosieribbons]

thank you to everyone who posted in this thread - collectively you have saved me about three days monotonous data entry. It's working! :D

xxx

 

[Robert Mika]

thank you to everyone who posted in this thread - collectively you have saved me about three days monotonous data entry. It's working! :D

xxx

You are welcome.