Index Match - want to return ALL matches, not just first match

[SpencerRichman]

I have a table of data like this:

		Annual Charges
File #	2012	2013	2014
120	$505.00	$0.00	$120.00
908	$15.00	$1,200.00	$1,425.00
862	$0.00	$0.00	$3,750.00
908	$2,000.00	$105.00	$25.00

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Notice that there are TWO rows with File #908 . I am trying to use Index Match to find the charges that match the file number in the first column and return the results of the charges in one of the annual charges columns, depending on the year. The traditional Index Match formula only returns the FIRST match of $1,425 for file #908 .

What I want is to return the SUM of ALL the matches for file #908 . But I still need to preserve the ability in the formula to choose the column based on another cell (in which I input the year that I'm working on). Any suggestions? (P.S. I'm working in Excel 2010).

 

[knacksc2]

would a sumif function work?

 

[SpencerRichman]

would a sumif function work?

I don't know how to make a Sumif function work when I need to have the formula dynamically choose the column to add...can you ?

 

[AhoyNC]

You could use SUMPRODUCT

Excel Workbook
	A	B	C	D	E	F	G
1	File #	2012	2013	2014		File #	908
2	120	$505.00	$0.00	$120.00		Year	2014
3	908	$15.00	$1,200.00	$1,425.00			$1,450.00
4	862	$0.00	$0.00	$3,750.00
5	908	$2,000.00	$105.00	$25.00
Sheet

 

[Eric W]

This is how to use SUMIF with a variable range:

	A	B	C	D	E	F	G
1	File #	2012	2013	2014		File #	908
2	120	$505.00	$0.00	$120.00		Year	2014
3	908	$15.00	$1,200.00	$1,425.00			$1,450.00
4	862	$0.00	$0.00	$3,750.00
5	908	$2,000.00	$105.00	$25.00

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

Sheet6​

Worksheet Formulas Cell Formula G3 =SUMIF(A2:A5,G1,INDEX(B2:D5,0,MATCH(G2,B2:D2))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[markmzz]

I have a table of data like this:

		Annual Charges
File #	2012	2013	2014
120	$505.00	$0.00	$120.00
908	$15.00	$1,200.00	$1,425.00
862	$0.00	$0.00	$3,750.00
908	$2,000.00	$105.00	$25.00

<tbody>
</tbody>

Notice that there are TWO rows with File #908 . I am trying to use Index Match to find the charges that match the file number in the first column and return the results of the charges in one of the annual charges columns, depending on the year. The traditional Index Match formula only returns the FIRST match of $1,425 for file #908 .

What I want is to return the SUM of ALL the matches for file #908 . But I still need to preserve the ability in the formula to choose the column based on another cell (in which I input the year that I'm working on). Any suggestions? (P.S. I'm working in Excel 2010).

Hi!

Try this In G2 and copy to the right

=SUMIF($A$2:$A$5,$F2,INDEX($B$2:$D$5,,MATCH(G$1,$B$1:$D$1,0)))

	A	B	C	D	E	F	G	H	I	J
1	File #	2012	2013	2014		File #	2012	2013	2014
2	120	505	0	120		908	2015	1305	1450
3	908	15	1200	1425
4	862	0	0	3750
5	908	2000	105	25
6
***	******	******	******	******	**	******	******	******	******	**

<tbody>
</tbody>

Markmzz

 

[SpencerRichman]

This is how to use SUMIF with a variable range:

	A	B	C	D	E	F	G
1	File #	2012	2013	2014		File #	908
2	120	$505.00	$0.00	$120.00		Year	2014
3	908	$15.00	$1,200.00	$1,425.00			$1,450.00
4	862	$0.00	$0.00	$3,750.00
5	908	$2,000.00	$105.00	$25.00

<tbody>
</tbody>

Sheet6​

Worksheet Formulas Cell Formula G3 =SUMIF(A2:A5,G1,INDEX(B2:D5,0,MATCH(G2,B2:D2))) <tbody> </tbody>

<tbody>
</tbody>

This worked perfectly, thank you!

 

[Eric W]

Glad we could help!