IF Statement or Macro Help

[mrickert]

Hello. I need some help please, here's my situation.

Column A has 90,000+ rows of text.

Coulmn B has a price.

Column C has 500+ rows of text.

Column D has a price.


I need to search for each item in Column C and see if it is in Column A. If so, I need to replace Column B with the price in Column D.


Example:

Column A     Column B     Column C      Column D
123456          1.25       FAS1SA          9.99
999994          2.50       32DAVB         10.50
DATA21        100.00       123456         99.99
123FAS         74.21       IOASHD         10.40
UIDF32          0.04       MASD54          1.11
 
 
Desired Result:
 
Column A     Column B     Column C      Column D
123456         99.99       FAS1SA          9.99
999994          2.50       32DAVB         10.50
DATA21        100.00       123456         99.99
123FAS         74.21       IOASHD         10.40
UIDF32          0.04       MASD54          1.11

Note the change in Column B for the value assocaited with 123456.

Not sure how to approach this. Thanks in advance.

 

[Robert Mika]

If you are in Excel 07 or 10
In E2 copy down
=IFERROR(INDEX($A$2:$D$6,MATCH(A2,$C$2:$C$6,0),4),B2)
In previous versions:

=IF(ISNUMBER(MATCH(A2,$C$2:$C$6,0)),INDEX($A$2:$D$6,MATCH(A2,$C$2:$C$6,0),4),B2)

 

[mrickert]

All that seems to do is give me the value of the the B column in the next row:

(Sorry, not sure how to post Excel code on here)


Any other ideas? Using Excel 97 if that matters.

 

[Robert Mika]

In youe example your data starts in row 2 or at least it looks like