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]

SHEET 1
LINE ITEM
A	Summed value from sheet 2
B	Summed value from sheet 2
C	Summed value from sheet 2
D	Summed value from sheet 2
SHEET 2
Line Item		Value 1	Value 2	Value 3	Value 4
A		423		423
B			423
C		234	324	423	23423
D			23423

<tbody>
</tbody>

Please help!

Summed value from sheet 2

<tbody>
</tbody>

 

[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))​