Formula Help!: Index/Match to return a range to use in Sumpr

[ajmckenna]

I need to multiply & sum my data in Row 4 x either row one (B1:E1) or row 2 (B2:B4) with result in Cell F6; I would like to do a lookup based on value in cell A6 to return the the appropriate range to use in the sumproduct formula. I figured I could use multiple if statements as I showed below. but would like to use a Index/Match and sumproduct as I will have more than the 2 options in "mysumproduct range 1" below
Sumproduct range 1(input table)
A B C D E F
Row 1 S1 .25 .25 .10 .40
Row 2 C1 .25 .50 .15 .10

Sumproduct range 2
Row 4 10 20 30 40

Row 6 S1
Formula in F6 = if($A6="S1",Sumproduct($B1:$E$1,B4:E4),if(A6="C1",Sumproduct($B2:$E$2,B4:E4),"error"))
(note F6 will be copied to down & to the right)
I would like to use and Index/Match to lookup the proper range based on the value placed in A6. My input table will not be sorted in acending order so I believe Lookup will not work. Is this possible? and can I use index match to return a range to use in the sumproduct?

Thanks guys!

 

[IML]

I kept getting a ref! error with

=SUMPRODUCT(INDEX($B$1:$E$2,MATCH(A6,$A$1:$A$2,0)),$B$4:$E$4)

but until a correct answer is posted on this, you should be able to get away with
=SUMPRODUCT($B$4:$E$4,OFFSET($B$1:$E$1,MATCH(A6,$A$1:$A$2,0)-1,0))

I think the index version would run a little smoother.
This message was edited by IML on 2002-11-01 10:38

 

[paelinck]

This is how I would solve your problem:

Book1
	A	B	C	D	E	F	G
1	S1	0.25	0.25	0.1	0.4		0
2	C1	0.25	0.5	0.15	0.1		1
3
4		10	20	30	40
5
6	C1	21
Sheet1

The formulas in G1 and G2 puts a "1" next to the row that corresponds with the selection in cell A6.

Please not that the formula in B6 is an array-formula. Do not type the braces around the formula and close the formula with Ctrl-Shift-Enter.

If you want to include new rows below row 2, copy down the formula in column G next to the new row(s) and extend the range B1:E2 in the formula of B6 to include the new row(s)

I hope my explanation is clear enough.

Marc

 

[paelinck]

Sorry, I forgot something in the last paragraph. This should be:

If you want to include new rows below row 2, copy down the formula in column G next to the new row(s) and extend the range B1:E2 in the formula of B6 to include the new row(s)
*and* extend the range G1:G2 in the same formula to include the new row(s) as well.

 

[IML]

=SUMPRODUCT($B$4:$E$4,OFFSET($B$1:$E$1,MATCH(A6,$A$1:$A$2,0)-1,0))

This is the only item that would need to be changed. Adjust a2 to A6 for 6 rows, etc. This assumes your $B$4:$e$4 would change to something else to do the insertion of row.

 

[ajmckenna]

Thanks Marc:

but the formula will be rolling so that I need to be able to copy the formula in B6 so that it will take the previous I.e B4:E4 will need to change to C4:f4 if I copy the formaula to the right.

Also, I will be using this formula for multiple lines I.e row 7 might use the S1 valuesnot the C1 values and therefore the formula in G1 will not work

I think IML's solution will work fine. The actual s1 and C2 input table will be in another workbook so hopefully it still works

thanks for all the help.

 

[Aladin Akyurek]

On 2002-11-01 11:30, ajmckenna wrote:
[...]
I think IML's solution will work fine. The actual s1 and C2 input table will be in another workbook so hopefully it still works
[..]

The INDEX version is...

=SUMPRODUCT(INDEX($B$1:$E$2,MATCH(A6,$A$1:$A$2,0),0),$B$4:$E$4)

as Ian also wanted you to have.