Two Vlookup and provide total of given account number

[bhatipuru]

Data

10/01/12</SPAN>	2000</SPAN>	3000</SPAN>	4000</SPAN>	5000</SPAN>	6000</SPAN>
1A</SPAN>	1.00 </SPAN>
2A</SPAN>		2.00 </SPAN>
3A</SPAN>			3.00 </SPAN>
4A</SPAN>				4.00 </SPAN>
5A</SPAN>					5.00 </SPAN>
Totals</SPAN>	1.00 </SPAN></SPAN>	2.00 </SPAN></SPAN>	3.00 </SPAN></SPAN>	4.00 </SPAN></SPAN>	5.00 </SPAN>
10/01/12</SPAN>	2000</SPAN>	3000</SPAN>	4000</SPAN>	5000</SPAN>	6000</SPAN>
1A</SPAN>
2A</SPAN>		2.00 </SPAN>
3A</SPAN>
4A</SPAN>				4.00 </SPAN>
5A</SPAN>					5.00 </SPAN>
6A</SPAN>	6.00 </SPAN>
Totals</SPAN>	6.00 </SPAN></SPAN>	2.00 </SPAN></SPAN>	- </SPAN></SPAN>	4.00 </SPAN></SPAN>	5.00 </SPAN>
10/01/12</SPAN>	2000</SPAN>	3000</SPAN>	4000</SPAN>	5000</SPAN>	6000</SPAN>
1A</SPAN>	1.00 </SPAN>
2A</SPAN>		2.00 </SPAN>
3A</SPAN>			- </SPAN>
4A</SPAN>				- </SPAN>
5A</SPAN>					- </SPAN>
6A</SPAN>					6.00 </SPAN>
7A</SPAN>			7.00 </SPAN>
Totals</SPAN>	1.00 </SPAN></SPAN>	2.00 </SPAN></SPAN>	7.00 </SPAN></SPAN>	- </SPAN></SPAN>	6.00 </SPAN>
10/01/12</SPAN>	2000</SPAN>	3000</SPAN>	4000</SPAN>	5000</SPAN>	6000</SPAN>
1A</SPAN>	1.00 </SPAN>
2A</SPAN>		- </SPAN>		2.00 </SPAN>
3A</SPAN>			- </SPAN>
4A</SPAN>				- </SPAN>	4.00 </SPAN>
Totals</SPAN>	1.00 </SPAN></SPAN>	- </SPAN></SPAN>	- </SPAN></SPAN>	2.00 </SPAN></SPAN>	4.00 </SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=6></COLGROUP>

New worksheet

Date</SPAN>	10/01/12</SPAN>
2000</SPAN>	1.00 </SPAN>
3000</SPAN>	2.00 </SPAN>
4000</SPAN>	3.00 </SPAN>
5000</SPAN>	4.00 </SPAN>
6000</SPAN>	5.00 </SPAN>

<TBODY>
</TBODY><COLGROUP><COL span=2></COLGROUP>

What I want is, when I populate the date, the totals for 2000,3000 etc should give me the total for the account for the date. I need a formula

 

[Robert Mika]

Data

10/01/12	2000	3000	4000	5000	6000
1A	1.00
2A		2.00
3A			3.00
4A				4.00
5A					5.00
Totals	1.00	2.00	3.00	4.00	5.00
10/01/12	2000	3000	4000	5000	6000
1A
2A		2.00
3A
4A				4.00
5A					5.00
6A	6.00
Totals	6.00	2.00	-	4.00	5.00
10/01/12	2000	3000	4000	5000	6000
1A	1.00
2A		2.00
3A			-
4A				-
5A					-
6A					6.00
7A			7.00
Totals	1.00	2.00	7.00	-	6.00
10/01/12	2000	3000	4000	5000	6000
1A	1.00
2A		-		2.00
3A			-
4A				-	4.00
Totals	1.00	-	-	2.00	4.00

<tbody>
</tbody>

New worksheet

Date	10/01/12
2000	1.00
3000	2.00
4000	3.00
5000	4.00
6000	5.00

<tbody>
</tbody>

What I want is, when I populate the date, the totals for 2000,3000 etc should give me the total for the account for the date. I need a formula

Are those -multiple table going down the sheet?
So the answer let say for 3000 will be 6?

 

[bhatipuru]

The answer I want is for 3000 is 2 for 10-01-12 and 10-04-12 (last table the date should be 10-04-12 instead of 10-01-12) = 0.

 

[Robert Mika]

The answer I want is for 3000 is 2 for 10-01-12 and 10-04-12 (last table the date should be 10-04-12 instead of 10-01-12) = 0.

First 3 tables are for 10-01-12.
Which one is the correct one?

 

[bhatipuru]

The tables should be 10-01-12, 10-02-12, 10-03-12 & 10-04-2.

 

[bhatipuru]

any update?

 

[Robert Mika]

any update?

Excel 2010

	A	B	C	D	E	F
1	Data
2	10/1/2012	2000	3000	4000	5000	6000
3
4	1A	1
5	2A		2
6	3A			3
7	4A				4
8	5A					5
9
10	Totals	1	2	3	4	5
11
12
13
14	10/2/2012	2000	3000	4000	5000	6000
15
16	1A
17	2A		2
18	3A
19	4A				4
20	5A					5
21	6A	6
22
23	Totals	6	2	-	4	5
24
25
26	10/3/2012	2000	3000	4000	5000	6000
27
28	1A	1
29	2A		2
30	3A			-
31	4A				-
32	5A					-
33	6A					6
34	7A			7
35
36	Totals	1	2	7	-	6
37
38
39	10/4/2012	2000	3000	4000	5000	6000
40
41	1A	1
42	2A		-		2
43	3A			-
44	4A				-	4
45
46	Totals	5555	-	-	2	4

<tbody>
</tbody>

Sheet1​

Excel 2010

	A	B
1	Date	10/4/2012
2	totals
3	2000	5555
4	3000	-
5	4000	-
6	5000	2
7	6000	4

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B3 =INDEX(Sheet1!$A$100:OFFSET(Sheet1!$A$2,MATCH($B$1,Sheet1!$A$2:$A$100,0)-1,6,,),MATCH($A$2,INDEX(Sheet1!$A$100:OFFSET(Sheet1!$A$2,MATCH($B$1,Sheet1!$A$2:$A$100,0)-1,,,),),0),MATCH($A3,INDEX(Sheet1!$A$2:$F$100,MATCH($B$1,Sheet1!$A$2:$A$100,0),0),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[bhatipuru]

I have made some adjustments but, this works perfect. Thanks.

 

[Robert Mika]

I have made some adjustments but, this works perfect. Thanks.

You are welcome.Thanks for feedback.