Sumproduct Help

[jessabell75]

I have a huge spreadsheet that updates monthly and I want to insert a column at the beginning for ease in reconciliation of the current balance and current AD. See example below...I basically want to update the date field on a monthly basis and get the current balances populated. Thinking this requires a sumproduct but I'm having trouble remembering how to set this up. Any help is appreciated!


Sheet1

	C	D	E	F	G	H	I	J	K	L	M

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 99px;"><col style="width: 99px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 12px;"><col style="width: 64px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: center"]Current Month[/TD]
[TD="align: center"]Current[/TD]
[TD="align: center"]Current[/TD]

[TD="align: center"]Depr[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]Balance[/TD]

[TD="align: center"]Depr[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]Balance[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: center"]2/1/2018[/TD]
[TD="align: center"]AD[/TD]
[TD="align: center"]Balance[/TD]

[TD="align: center"]1/1/2018[/TD]
[TD="align: center"]1/1/2018[/TD]
[TD="align: center"]1/1/2018[/TD]

[TD="align: center"]2/1/2018[/TD]
[TD="align: center"]2/1/2018[/TD]
[TD="align: center"]2/1/2018[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"]6000[/TD]

[TD="align: right"]1500[/TD]
[TD="align: right"]2500[/TD]
[TD="align: right"]3000[/TD]

[TD="align: right"]4800[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]6000[/TD]

</tbody>

 

[Scott T]

try

Excel 2010
	C	D	E	F	G	H	I	J	K	L	M
1	Current Month	Current	Current		Depr	AD	Balance		Depr	AD	Balance
2	2/1/2018	AD	Balance		1/1/2018	1/1/2018	1/1/2018		2/1/2018	2/1/2018	2/1/2018
3		500	6,000		1,500	2,500	3,000		4,800	500	6,000
Sheet1

Cell Formulas
Range	Formula
D3	{=INDEX($G$3:$M$3,MATCH($C2&D2,$G$2:$M$2&$G$1:$M$1,0))}
E3	{=INDEX($G$3:$M$3,MATCH($C2&E2,$G$2:$M$2&$G$1:$M$1,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.