VBA to find/replace information from one sheet to another

Question

I am looking for help on writing a VBA that will replace information in one sheet using a legend that is in another sheet.

I need two things to happen. 1. replace cells in column C in Sheet1 with data from column C in Sheet2 if column C in Sheet1 = column A in Sheet2. 2. also insert the adjacent cell (Item Number) that corresponds with the new description.

Here is a simple sample of the spreadsheet.

Sheet 1 BEFORE Code:

1    A                             B                 C
2   EQUIPMENT TYPE             ITEM NUMBER      DESCRIPTION
3   Actives                                        NC400
4   Full Spectrum EQ- Bank 1                       T2&4 CS12
5                                                  T2&4 CS09
6   Full Spectrum EQ - Bank 2                      T8 CS09  
7                                                  T8 CS06

Sheet 2 (legend)

1               A                B                  C
2   Original Description    Item Number     New Description
3           NC400               5543          Full Features 2x2
4           T2&4 CS12           5568          Cable Simulator 12db
5           T2&4 CS09           9956          Cable Simulator 9db
6           T8 CS09             5547          Return Path 9db
7           T8 CS06             6689          Return Path 6db

Sheet 1 AFTER CODE:

1             A                      B              C
2   EQUIPMENT TYPE              ITEM NUMBER      DESCRIPTION
3   Actives                       5543              Full Features 2x2
4   Full Spectrum EQ- Bank 1      5568              Cable Simulator 12db
5                                 9956              Cable Simulator 9db
6   Full Spectrum EQ - Bank 2     5547              Return Path 9db
7                                 6689              Return Path 6db

I know that I will have to use a series of loops, I would appreciate any help.

Thanks!

Answer 1

Hey Mate give this a crack:

=INDEX(Sheet2!$B$2:$B$7,MATCH(Sheet1!$C2,Sheet1!C2:C7,0))  

Alternative would be a vlookup but index/match is best practice.

Here's a tutorial: LINK

No VBA Necessary...