Index Match to Sum full row

[masterschef]

[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]Hi all,[/FONT]


[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]I'm trying to build (what I thought to be a simple) Excel formula that matches a line item from one sheet with another line item from a second sheet and sums up all values in that matched row.[/FONT]

[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]This formula needs to be flexible to accommodate new columns so I was hoping to use a mix of SUMIF and INDEX/MATCH.
[/FONT]

[TABLE="width: 500"]
<tbody>[TR]
[TD]SHEET 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]LINE ITEM
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Summed value from sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Summed value from sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Summed value from sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Summed value from sheet 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SHEET 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Line Item[/TD]
[TD][/TD]
[TD]Value 1[/TD]
[TD]Value 2[/TD]
[TD]Value 3[/TD]
[TD]Value 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD]423[/TD]
[TD][/TD]
[TD]423[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD]423[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD]234[/TD]
[TD]324[/TD]
[TD]423[/TD]
[TD]23423[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD]23423[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Please help!







[TABLE="width: 500"]
<tbody>[TR]
[TD]Summed value from sheet 2[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Try...

=SUM(INDEX(Sheet2!B:E,MATCH($A2,Sheet2!A:A,0),0))

 

[masterschef]

THANK YOU!

That seems to work perfectly!

 

[Aladin Akyurek]

THANK YOU!

That seems to work perfectly!

You are welcome.

 

[masterschef]

Sorry for the followup

Is there a way to rewrite this so that it doesn't reference the enter column? I'm working with a large workbook that has ballooned to over 654MB. If I try to reference a cell range instead of full column, no data is returned

 

[Aladin Akyurek]

Sorry for the followup

Is there a way to rewrite this so that it doesn't reference the enter column? I'm working with a large workbook that has ballooned to over 654MB. If I try to reference a cell range instead of full column, no data is returned

Of course...

=SUM(INDEX(Sheet2!$B$2:$E$5,MATCH($A2,Sheet2!$A$2:$A$5,0),0))​