sum problem Hlookup;vlookup ?

[pinstripe]

sheet1
--tz tu tv to
1| 5 6 4 5
2| 1 7 1 2
3| 4 4 6 3
4| 9 4 6 4

sheet2
columnA ColumnB ColumnC
1------------ tv
2 ----------- tu
2 ------------ tz
4 ------------to
4-------------tu
3 -------------tz

if i have criteria row "2" and column "tu" -- i need to sum 1+7 = 8
if i have criteria row "4" and column "to" -- i need to sum 9+4+6+4=23
and so on

I need to have results in ColumnC....how to manage this?
thanx in advance

[daddylonglegs]

Assuming your sheet1 table has tz, tu etc. in B1:E1, 1,2,3,4, in A2:A5 and your values to sum in B2:E5 try this formula in sheet2 C2 copied down

=SUM(OFFSET(Sheet1!B$1,MATCH(A2 ,Sheet1!A$2:A$5,0),,,MATCH(B2 ,Sheet1!B$1:E$1,0)))

[NBVC]

Assuming setup as attached sheet....(ie. Lookup table in Sheet1!A1:D5 and Input data in Sheet2, beginning in A2 and B2...)

You can use formula:

Please Login or Register  to view this content.

copied down.

[pinstripe]

Assuming your sheet1 table has tz, tu etc. in B1:E1, 1,2,3,4, in A2:A5 and your values to sum in B2:E5 try this formula in sheet2 C2 copied down

=SUM(OFFSET(Sheet1!B$1,MATCH(A2 ,Sheet1!A$2:A$5,0),,,MATCH(B2 ,Sheet1!B$1:E$1,0)))

thank you both for fast reply.
@daddylonglegs --> your formula is 100% correct for what i have asked for...but i need to do some modification beacuse of an upgrade.
can you please help me further with this?
Now, i have another criteria to look for (there is new column added with another code)
* - are not in table;used just here
item|code|tz tu tv to
1 |---8---| 5 6 4 5
2 |---7---| 1 7 1 2
3 |---8---| 4 4 6 3
4 |---3---| 9 4 6 4
need to sum just those with code 8 (number 8 is placed in sheet2 Z1)
(if item match and if code match then sum like you did in your formula)
how to put this criteria into your formula
=SUM(OFFSET(Sheet1!B$1,MATCH(A2 ,Sheet1!A$2:A$5,0),,,MATCH(B2 ,Sheet1!B$1:E$1,0)))[/QUOTE]
thanks in advance

[pinstripe]

okay,
i managed to fix this for additional criteria with & in MATCH case.

=SUM(OFFSET(Sheet1!BC$1,MATCH(A2&Z1 ,Sheet1!A$2:A$5&Sheet1!B$2:B$5,0),,,MATCH(C2 ,Sheet1!C$1:F$1,0)))

so disregard the previous post.
But, here is another problem:
to previous table are added some more columns with:

item|code|tz|tu|tv|to|zt|zu|zv|zo
1 |---8---| 5 |6 |4 |5 |4 |6 |2 |1
2 |---7---| 1 |7 |1 |2 |5 |7 |3 |1
3 |---8---| 4 |4 |6 |3 |1 |2 |4 |3
4 |---3---| 9 |4 |6 |4 |2 |3 |1 |2


if i have criteria item "2" and column "tu" -- i need to sum 1+7 = 8
if i have criteria item "4" and column "to" -- i need to sum 9+4+6+4=23
but:
if i have criteria item "2" and column "zt" -- i need to sum 4 = 4
if i have criteria item "3" and column "zv" -- i need to sum 1+2+4=7
and so on

can you help?