Using vlookup with sum/average

[city]

Hi,

Please help me with this. This is really urgent that i need to learn this. I really don't know how to use vlookup and sum/average function together.

I already tried AVERAGE(VLOOKUP(A2,data!A$2:C$13,3,FALSE)), but it only returns the first value in the data tab.



I was thinking that vlookup data should be placed in an array.. but don't know how... =(

Pivot table is placed there only as reference as to see if calculations will be correct.

Can someone please educate me on this.. I would really appreciate this. Thank you.

[NBVC]

Use these:

=SUMIF(data!A$2:A$3,A2,data!C$2:C$13)

and

=AVERAGEIF(data!A$2:A$3,A2,data!C$2:C$13)

[city]

that works. Thanks. =)

but is there any other methods, the assignment was to use the vlookup to get the sum/average.. given that if in the data table, joe1 was entered. . it would still be able to get the sum/average.. ..

[NBVC]

If there are going to be duplicated matches (i.e. name appears twice in same month) then vlookup won't work.... If there is only one appearance of the name per month, then you don't need to sum or average at all... so I am not sure what the issue is then.

[city]

i mean, if we were to put a new data since this would be a running data. and the data inputed is (row 14: joe1, march 2011, 230).

Is there a way where we could probably catch it in the calculation of the sum/average of joe.

I apologize if Im not that clear. . .

[NBVC]

Use dynamic named ranges then: http://www.contextures.com/xlnames01.html#Dynamic

[Haseeb Avarakkan]

Or,

You can use whole column reference.

B2, copy down.

=SUMIF(data!A:A,A2,data!C:C)

C2, copy down.

=IFERROR(AVERAGEIF(data!A:A,A2,data!C:C),"")

B11, copy down.

=SUMIF(data!B:B,">="&A11,data!C:C)-SUMIF(data!B:B,">"&EOMONTH(A11,0),data!C:C)

C11, copy down.

=IFERROR(AVERAGEIFS(data!C:C,data!B:B,">="&A11,data!B:B,"<="&EOMONTH(A11,0)),"")

A11 to down, should be first day of the month.

[NBVC]

The point was the OP was asking to sum or average results using VLOOKUP...

[city]

still can't get it. . . Is there a way to capture all vlookup caputured data in an array?

=VLOOKUP(A2,data!A$2:C$13,3,True) will read the name even if joe1 is entered, but if you where to arrange it in ascending order it only captures the closest value not the exact.

[Miraun]

No, Vlookup responds with the first instance of the array that matches the criteria.

If the endgame of the equation is to obtain the average or sum or all situations where the conditions occur... then you'll need to go with the averageif or sumif equations. I believe you may be able to use wildcards in the Averageif or Sumif equations to reflect the variations on the fuzzy search between joe and joe1 which you would get with the "True" function at the end of the Vlookup.

[city]

ok. Thanks for the help guys. That means I have to go on and use wildcards...

=SUMIF(name,A2&"*",prod)
=IFERROR(AVERAGEIF(data!A:A,A2&"*",data!C:C),"")

I did thought that lookup could be used to actualy be used to error check or something. .