Need help on index and match function
 

I'm given these two columns A and B where certain (made up) codes need to have their Amounts adjusted manually, from
time to time.
In order to avoid searching for each code manually to adjust (not always in order as shown below in columns A and B, and
the number of codes vary). I am trying to be able to manually type in columns C and D with the required changes (only
for the affected codes and their quanities) and have their result reflected in the new columns E and F, as shown below.
I have tried using a combination of the match and index function together but have not been successful. Your help in
this mater would be appreciated.

Thank you

Terry


Original data given Data needed to be Final Data
modified / adjusted manually (New)

A B C D E F
1 Code Amount Code Amount Code Amount
2 C001 1000 C003 -500 C001 1000
3 C002 2000 C005 1000 C002 2000
4 C003 3500 C007 2500 C003 3000
5 C004 2500 " " C004 2500
6 C005 9000 " " C005 10000
7 C006 5000 " " C006 5000
8 C007 2000 C007 4500
" " " " "
" " " " "
501 C500 8000 C500 8000

=INDEX(Amount_Range,MATCH(code,Code_Range,0))

as an example how index and match can be combined, I am not sure I
understand what you are trying to do though, maybe your posted table is
warped a bit because I can't make sense of it.

Regards,

Peo Sjoblom

"Susan Hayes" wrote:

I'm given these two columns A and B where certain (made up) codes need to have their Amounts adjusted manually, from
time to time.
In order to avoid searching for each code manually to adjust (not always in order as shown below in columns A and B, and
the number of codes vary). I am trying to be able to manually type in columns C and D with the required changes (only
for the affected codes and their quanities) and have their result reflected in the new columns E and F, as shown below.
I have tried using a combination of the match and index function together but have not been successful. Your help in
this mater would be appreciated.

Thank you

Terry


Original data given Data needed to be Final Data
modified / adjusted manually (New)

A B C D E F
1 Code Amount Code Amount Code Amount
2 C001 1000 C003 -500 C001 1000
3 C002 2000 C005 1000 C002 2000
4 C003 3500 C007 2500 C003 3000
5 C004 2500 " " C004 2500
6 C005 9000 " " C005 10000
7 C006 5000 " " C006 5000
8 C007 2000 C007 4500
" " " " "
" " " " "
501 C500 8000 C500 8000

Hi
try the following formulas:
E2:
=A2
and copy down for all rows

F2:
=SUMIF(A:A,E2,B:B)+SUMIF(C:C,E2:D:D)
and also copy down

--
Regards
Frank Kabel
Frankfurt, Germany

Susan Hayes wrote:
I'm given these two columns A and B where certain (made up) codes
need to have their Amounts adjusted manually, from time to time.
In order to avoid searching for each code manually to adjust (not
always in order as shown below in columns A and B, and the number of
codes vary). I am trying to be able to manually type in columns C
and D with the required changes (only for the affected codes and
their quanities) and have their result reflected in the new columns E
and F, as shown below. I have tried using a combination of the match
and index function together but have not been successful. Your help
in this mater would be appreciated.

Thank you

Terry


Original data given Data needed to be Final Data
modified / adjusted manually (New)

A B C D E F
1 Code Amount Code Amount Code Amount
2 C001 1000 C003 -500 C001 1000
3 C002 2000 C005 1000 C002 2000
4 C003 3500 C007 2500 C003 3000
5 C004 2500 " " C004 2500
6 C005 9000 " " C005 10000
7 C006 5000 " " C006 5000
8 C007 2000 C007 4500
" " " " "
" " " " "
501 C500 8000 C500 8000