Search code examples
excelexcel-formulaexcel-2010excel-2007

Super Replace Function in Excel

I have 12,000 cells in an excel sheet, each cell has one out of 2,000 categories (so, there are several cells with the same category), something like;

1- A
2- B
3- C
4- A
5- A
6- B
7- AA
8- AB
...
12000- AAZ

And I have another sheet containing two columns with a number for each category (column A= the category name & column B= the corresponding value), like;

column A   column B
A           1.5
B           2.3

......

Now, I want any possible function to help me replace the values in these 12000 cells with the corresponding number. Thanks in advance.

Solution

  • Assumption: Sheet1 has your 12,000 cells in column A

    Assumption: Sheet2 is your reference sheet

    Then in cell Sheet1!B1:

    INDEX/MATCH

    =INDEX(Sheet2!A:B,MATCH(A1,Sheet2!A:A,0),2)

    VLOOKUP

    =VLOOKUP(A1,Sheet2!A:B,2,FALSE)

    On a large dataset use the first option as it is faster!