Help with formulae to select rates

[moofish87]

Hi,

I'm looking for a formulae that will select a row of rates to Sumproduct based on the variable at the start of the row.

Ive crudely mocked up the spreadsheet below. so when the variable is inputted into column 2 it will select the row of rates 2 and sumproduct them against that row.

Hopefully this makes sense and thansk for your help.

 

[Eric W]

One way:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J	K	L
1
2			1	$ 4.00	$ 8.00	$ 12.00	$ 16.00	$ 20.00	$ 24.00	$ 28.00	$ 32.00
3			2	$ 7.00	$ 14.00	$ 21.00	$ 28.00	$ 35.00	$ 42.00	$ 49.00	$ 56.00
4			3	$ 5.00	$ 10.00	$ 15.00	$ 20.00	$ 25.00	$ 30.00	$ 35.00	$ 40.00
5			4	$ 1.00	$ 2.00	$ 3.00	$ 4.00	$ 5.00	$ 6.00	$ 7.00	$ 8.00
6	Address	Variable										Total
7	Street1	2			1		1			1		91
8	Street2	1				1		1				32
9	Street3	4		1					1			7
Sheet16

Cell Formulas
Range	Formula
L7:L9	L7	=SUMPRODUCT(D7:J7,INDEX($D$2:$J$5,MATCH(B7,$C$2:$C$5,0),0))

 

[moofish87]

You have just saved me soo much time. I can not thank you enough.

 

[Eric W]

Happy to help! ?