Prev: combining columns and using concatenate
Next: Return cell characters after space
From: CherylK on 20 Apr 2010 18:25
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?



From: Max on 20 Apr 2010 19:10
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?
>
>
>
 | 
Pages: 1
Prev: combining columns and using concatenate
Next: Return cell characters after space