Picking Values from a Table

[Steve 1962]

Hi All

I am inputting values into row 2 under certain labels. Each label has a value which corresponds with the values in table A5:B9. The total is then shown in cell B12 (product of a multiplication).

I am trying to remove the intermediate step which requires table D12:E16. I assume a MATCH / INDEX / HLOOKUP or SUMPRODUCT formula may be the answer but I'm not sure.

There are only 5 labels shown here but I need the flexibility to expand.

Thanks

Book1
	A	B	C	D	E
1	ABC	DEF	GHI	JKL	MNO
2	2	0	5	0	6
3
4
5	ABC	16.1
6	DEF	12.8
7	GHI	15.4
8	JKL	11.4
9	MNO	12.5
10
11
12	Total	184.2		ABC	32.2
13				DEF	0.0
14				GHI	77.0
15				JKL	0.0
16				MNO	75.0
Sheet2

Cell Formulas
Range	Formula
B12	=SUM(E12:E16)
E12	=A2*B5
E13	=B2*B6
E14	=C2*B7
E15	=D2*B8
E16	=E2*B9

 

[Fluff]

How about
=SUMPRODUCT((A1:E1=A5:A9)*(A2:E2)*(B5:B9))

 

[Steve 1962]

Hey thanks very much Fluff. Works a breeze.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Steve 1962]

Hi Fluff

I've found one small issue. If the values in B5:B9 are arrived at using a formula, I get the #Value ! error.

Is there a way around this so that I can use the values that are from a formula ?

Thanks

 

[Fluff]

The fact that you have formulae in B5:B9 shouldn't make any difference, unless one or more of them is returning #VALUE ! or "", in which case you need to change that formula so that it returns 0

 

[Steve 1962]

Correct again Fluff. I did have an IF statement that returned a "" if true. I have now changed this to return 0.

All works fine again thanks.

 

[Fluff]

Glad to help & thanks for the feedback