Hi,
this is the sheet (A1="name"; A2="A ltd"; ... ; B1="jan"; ... ... ...):

name jan feb
Altd 5 8
Bltd 10 9
Cltd 7 11

In cells AA21 this formula (9 the result) VLOOKUP(A20,A1:C4,3,FALSE)) where
AA20 = "Bltd".

Now the problem: how can put in AA22 the SUM of "name" using VLOOKUP? (no
code VBA)

Example: if AA20 ="B ltd" the AA22 will be the sum of 10 and 9 = 19

Thanks in advance
M.

Hi

AA22=SUMPRODUCT((A1:A4=AA20)*(B1:B4+C1:C4))
or
AA22=SUMIF(A1:A4,AA20,B1:C4))


Arvi Laanemets


"Mire" wrote in message
...
Hi,
this is the sheet (A1="name"; A2="A ltd"; ... ; B1="jan"; ... ... ...):

name jan feb
Altd 5 8
Bltd 10 9
Cltd 7 11

In cells AA21 this formula (9 the result) VLOOKUP(A20,A1:C4,3,FALSE))
where
AA20 = "Bltd".

Now the problem: how can put in AA22 the SUM of "name" using VLOOKUP? (no
code VBA)

Example: if AA20 ="B ltd" the AA22 will be the sum of 10 and 9 = 19

Thanks in advance
M.

Mire,
One way
=SUM(OFFSET(INDIRECT("B"&MATCH(AA20,A1:A4,0)),0,0, 1,12))
this will sum the numbers in 12 columns starting from colB of the matching
row.
HTH
Cecil

"Mire" wrote in message
...
Hi,
this is the sheet (A1="name"; A2="A ltd"; ... ; B1="jan"; ... ... ...):

name jan feb
Altd 5 8
Bltd 10 9
Cltd 7 11

In cells AA21 this formula (9 the result) VLOOKUP(A20,A1:C4,3,FALSE))
where
AA20 = "Bltd".

Now the problem: how can put in AA22 the SUM of "name" using VLOOKUP? (no
code VBA)

Example: if AA20 ="B ltd" the AA22 will be the sum of 10 and 9 = 19

Thanks in advance
M.

Very good!
Thanks to Cecil and Arvi

"Cecilkumara Fernando" ha scritto nel messaggio
...
Mire,
One way
=SUM(OFFSET(INDIRECT("B"&MATCH(AA20,A1:A4,0)),0,0, 1,12))
this will sum the numbers in 12 columns starting from colB of the matching
row.
HTH
Cecil

"Mire" wrote in message
...
Hi,
this is the sheet (A1="name"; A2="A ltd"; ... ; B1="jan"; ... ... ...):

name jan feb
Altd 5 8
Bltd 10 9
Cltd 7 11

In cells AA21 this formula (9 the result) VLOOKUP(A20,A1:C4,3,FALSE))
where
AA20 = "Bltd".

Now the problem: how can put in AA22 the SUM of "name" using VLOOKUP?
(no
code VBA)

Example: if AA20 ="B ltd" the AA22 will be the sum of 10 and 9 = 19

Thanks in advance
M.