Sum and Vlookup Array Problem

[Graham]

I am trying to create an array formula shown below and cannot seem to get it
to do exactly what I am after.

{=SUM((C17:C21)*(VLOOKUP((E17:E21),Fertiliserunits,3,FALSE)%))}

The Vlookup picks up the values that I need exactly. When I enter a value in
C17 and E17 I get the sum values I want. However when I enter more data in
cell C18 and E18, it adds on the value of C18 times the lookup value in
cell E17, not the lookup value from E18. This is repeated in cell E19 which
adds on the value fromE19 times the E17 lookup. I hope this explains the
problem. Is there a way to do this with one formula or do I need to break it
down? Grateful for any guidance.

Kind regards
Graham Haughs
Turriff, Scotland.

 

[Frank Kabel]

Hi
what are you trying to do?
VLOOKUP will always return the first occurence of a match. Maybe
SUMPRODUCT or SUMIF is what you're looking for. e.g.
=SUMPRODUCT(C17:C21,E17:E21)

 

[Peo Sjoblom]

Try this

=SUMPRODUCT((LOOKUP(E17:E21,INDEX(Fertiliserunits,,1),INDEX(Fertiliserunits,
,3))),C17:C21)/100

 

[Graham]

Thanks to both for your help. Have now managed to get it to work using
permutation of Peo's post.

Kind regards
Graham