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

[SpencerRichman]

I have a table of data like this:
[TABLE="width: 389"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Annual Charges[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]File #[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[/TR]
[TR]
[TD="align: right"]120[/TD]
[TD="align: right"]$505.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$120.00[/TD]
[/TR]
[TR]
[TD="align: right"]908[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$1,200.00[/TD]
[TD="align: right"]$1,425.00[/TD]
[/TR]
[TR]
[TD="align: right"]862[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$3,750.00[/TD]
[/TR]
[TR]
[TD="align: right"]908[/TD]
[TD="align: right"]$2,000.00[/TD]
[TD="align: right"]$105.00[/TD]
[TD="align: right"]$25.00[/TD]
[/TR]
</tbody>[/TABLE]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]File #[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]2012[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]2013[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=C0C0C0]#C0C0C0[/URL] , align: center"]2014[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]File #[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]908[/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]120[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]$505.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]$0.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]$120.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]Year[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]2014[/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]908[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]$15.00 [/TD]
[TD="bgcolor: #
FFFFFF, align: right"]$1,200.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]$1,425.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]$1,450.00 [/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]862[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]$0.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]$0.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]$3,750.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: center"]908[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]$2,000.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]$105.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"]$25.00 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] , align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=SUMIF(A2:A5,G1,INDEX(B2:D5,0,MATCH(G2,B2:D2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[markmzz]

I have a table of data like this:
[TABLE="width: 389"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Annual Charges[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]File #[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[/TR]
[TR]
[TD="align: right"]120[/TD]
[TD="align: right"]$505.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$120.00[/TD]
[/TR]
[TR]
[TD="align: right"]908[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$1,200.00[/TD]
[TD="align: right"]$1,425.00[/TD]
[/TR]
[TR]
[TD="align: right"]862[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$3,750.00[/TD]
[/TR]
[TR]
[TD="align: right"]908[/TD]
[TD="align: right"]$2,000.00[/TD]
[TD="align: right"]$105.00[/TD]
[TD="align: right"]$25.00[/TD]
[/TR]
</tbody>[/TABLE]

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


[TABLE="class: grid, width: 462"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]File #[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD][/TD]
[TD]File #[/TD]
[TD="align: right"]2012[/TD]
[TD="align: right"]2013[/TD]
[TD="align: right"]2014[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"]120[/TD]
[TD="align: right"]505[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]120[/TD]
[TD][/TD]
[TD="align: right"]908[/TD]
[TD="align: right"]2015[/TD]
[TD="align: right"]1305[/TD]
[TD="align: right"]1450[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"]908[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1425[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"]862[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3750[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD="align: right"]908[/TD]
[TD="align: right"]2000[/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[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]
[TD]**[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz

 

[SpencerRichman]

This is how to use SUMIF with a variable range:

	A	B	C	D	E	F	G
120

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]File #[/TD]
[TD="align: center"]2012[/TD]
[TD="align: center"]2013[/TD]
[TD="align: center"]2014[/TD]
[TD="align: right"][/TD]
[TD="align: center"]File #[/TD]
[TD="align: center"]908[/TD]

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

[TD="align: right"]$505.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$120.00[/TD]
[TD="align: right"][/TD]
[TD="align: center"]Year[/TD]
[TD="align: center"]2014[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]908[/TD]
[TD="align: right"]$15.00[/TD]
[TD="align: right"]$1,200.00[/TD]
[TD="align: right"]$1,425.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$1,450.00[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]862[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$0.00[/TD]
[TD="align: right"]$3,750.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]908[/TD]
[TD="align: right"]$2,000.00[/TD]
[TD="align: right"]$105.00[/TD]
[TD="align: right"]$25.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]G3[/TH]
[TD="align: left"]=SUMIF(A2:A5,G1,INDEX(B2:D5,0,MATCH(G2,B2:D2)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This worked perfectly, thank you!

 

[Eric W]

Glad we could help!