finding and replacing multiple values

Hi there,

I'm currently doing a project which requires me to find and replace a large number of values.

ie. Column A Row 1 = 80099, Column A Row 2 = 554441, Column A Row 3 = Z5478, CAR4 = B89877 etc...


I need to replace these numbers with meaningful values and I have a reference table to tell me what these codes mean ie. 80099 = apple juice, 554441 = orange puree, Z5478 = pumpkin soup, B89877 = banana smoothie. The reference table is in a separate excel spreadsheet and is tabulated in two columns ie. Column A = "the code", Column B = "the value".

There are like 1000+ codes I need to find and replace which I know is going to kill me if I did it manually. How do I find and replace all the values?

Thank you....

Originally Posted by Glenn Kennedy

Separate sheet in the same workbook (one xlsx file), or on a separate workbook (two xlsx files). can you post an example sheet(s)?

Hi there Glen,

The reference table is on a separate workbook (another xlsx file).

I don't know how to use the Vlookup function

Originally Posted by Glenn Kennedy

You probably would be better off with an INDEX:MATCH. See the example sheets attached here.

Hi Glen, thanks for the solution. It looks simple enough. But before I apply the formula to 80,000 values, I have one more question. What happens if there are duplicate values on the source xlsx? ie. 1234 = banana, 1234 = banana (yellow), 1234 = BANANA

Post an small excel file, without confidential information.

Place the other data on sheet2 (just for this example).

Please also add the duplicated as you mentioned also in your file.

Here you go.

Yeah... the reference codes I have are very confusing but I've managed to solve the problem (kinda). Thanks everybody!

Oh sure,

I went with what you suggested (ie. the INDEX:MATCH method) and it worked beautifully, except that the duplicate values in the reference table was causing havoc. When faced with a duplicate, Excel will go with the first value it encounters and ignores the other duplicates. There's no way around this except to fix the reference table. Good thing a colleague emailed me an updated reference table with less duplicate values and that sorta fixed the issue.