I have 6 columns and many rows in a spreadsheet i want to create a formula
that looks at two common columns and if the same replace a column with an
amount from another column. Below is sort of an example of what I mean:

column a column b column c column d column e column f
acc50138 ACCCS20-1 6.14 acc50138 acccs20-1 10.00
acc50140 Prevention 31.05 acc50269 RTU 15.00
acc50379 concentrate 5.00 acc50379 concentrate 5.00

so what i need is the formula to look at column a and d if they are the same
then replace column c with the amount of column f. Any suggestions?

You can put this in G2:
=IF(A2="","",IF(ISNA(MATCH(A2,D:D,0)),C2,INDEX(F:F ,MATCH(A2,D:D,0))))
Copy down to return required results. Then copy col G and overwrite col C
with a paste special as values. Clear col G. You're done in about 10-15 secs.
Inspiring? hit the YES below
--
Max
Singapore
---
"CherylK" wrote:
I have 6 columns and many rows in a spreadsheet i want to create a formula
that looks at two common columns and if the same replace a column with an
amount from another column. Below is sort of an example of what I mean:

column a column b column c column d column e column f
acc50138 ACCCS20-1 6.14 acc50138 acccs20-1 10.00
acc50140 Prevention 31.05 acc50269 RTU 15.00
acc50379 concentrate 5.00 acc50379 concentrate 5.00

so what i need is the formula to look at column a and d if they are the same
then replace column c with the amount of column f. Any suggestions?