Using MATCH here
 

hi all of you,

I have one problem need your help

A sheet with the data below

TABLE 1 TABLE 2
INDEX Q'TY PRICE TOTAL INDEX Q'TY TOTAL AVG
1 100 2 200 12 SUM(B3+B4) SUM(D3+D4)
2 200 3 600 13 SUM(B3+B5) SUM(D3+D5)
3 300 4 1,200 14 SUM(B3+B6) SUM(D3+D6)
4 400 5 2,000 15
5 500 6 3,000 23
24
25
34
35
123 SUM(B3+B4+B5) SUM(D3+D4+D6)


Problem is to solve as
The function in column "Q'TY" in table 2 will lookup the number (value) in colume "INDEX" in table 2 to colume "INDEX" in table 1
then calculate the quantity. In my case as number 12 in colum "INDEX" in table 2 is a combination between number 1 and 2, so SUM(B3+B4)

Similarly to the culume "TOTAL" in table 2

Please, anyone can help to solve this function, VBA, or macro

Thanks in advance!

Using MATCH here
 

Try
=INDEX(B:B,MATCH(VALUE(LEFT(F2)),A:A,0))+INDEX(B:B ,MATCH(VALUE(RIGHT(F2)),A:A,0))

in place of SUM(B3+B4) (this is really SUM(B2+B3), check it!) and expand the
logic to the other formulae!

Regards,
Stefi

€žtimgiaiphap€� ezt Ã*rta:


hi all of you,

I have one problem need your help

A sheet with the data below

TABLE 1 TABLE 2
INDEX Q'TY PRICE TOTAL INDEX Q'TY TOTAL AVG
1 100 2 200 12 SUM(B3+B4) SUM(D3+D4)
2 200 3 600 13 SUM(B3+B5) SUM(D3+D5)
3 300 4 1,200 14 SUM(B3+B6) SUM(D3+D6)
4 400 5 2,000 15
5 500 6 3,000 23
24
25
34
35
123 SUM(B3+B4+B5) SUM(D3+D4+D6)


Problem is to solve as
The function in column "Q'TY" in table 2 will lookup the number (value)
in colume "INDEX" in table 2 to colume "INDEX" in table 1
then calculate the quantity. In my case as number 12 in colum "INDEX"
in table 2 is a combination between number 1 and 2, so SUM(B3+B4)

Similarly to the culume "TOTAL" in table 2

Please, anyone can help to solve this function, VBA, or macro

Thanks in advance!


+-------------------------------------------------------------------+
|Filename: Book2.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=129|
+-------------------------------------------------------------------+



--
timgiaiphap