Look up values in range and find maximum value in column

[rsutton1981]

Hi,

I am looking for a code that will look for a parameter in a column, search another sheet for all instances of the parameter and then find the maximum value recorded against the parameter.

e.g
in the review sheet I have a column with apples, oranges, etc
I want to search the assessment sheet for all instances of apples and then take the maximum value in Column L, and place it next to the apples in the review sheet.
I also need the same calculation to do the average rather then the max.

This has had me stumped all day.

Thanks in advance

 

[kweaver]

You could use an array formula (Ctrl+Shift+Enter) for the largest: =MAX((A1=Assessment!$A$1:$A$100)*Assessment!$L$1:$L$100)

And another array formula for the average: =AVERAGE((A1=Assessment!$A$1:$A$100)*Assessment!$L$1:$L$100)

But sent the upper limit of the ranges to whatever you need.

 

[rsutton1981]

Brilliant, been trying to sort with VBA and the array worked so well

 

[Eric W]

That AVERAGE formula won't work, since it will include a lot of 0 values. Maybe something like:

=AVERAGE(IF(Assessment!$A$1:$A$100=A1,Assessment!$L$1:$L$100))

with CSE.

or

=AVERAGEIF(Assessment!$A$1:$A$100,A1,Assessment!$L$1:$L$100)

normally entered.

 

[kweaver]

If you want to not include the zeros in the average, you might try: =SUMIF(Assessment!$A$1:$A$100,A1,Assessment!$L$1:$L$100)/COUNTIFS(Assessment!$A$1:$A$100,A1,Assessment!$L$1:$L$100,"<>0")

HA! or Eric's more simplistic version.

 

[rsutton1981]

Thank you both. the averages look much better now

 

[rsutton1981]

is there a way of adding a second search parameter to the formula. ie if(Assessment!$O$1:$O$100)="Y" and SUMIF(Assessment!$A$1:$A$100,A1,Assessment!$L$1:$L$100)/COUNTIFS(Assessment!$A$1:$A$100,A1,Assessment!$L$1:$L$100,"<>0")

 

[Eric W]

You should update your user profile to show which version of Excel you're using, since that affects the functions that you can use. For example, if you have the AVERAGEIFS function, try

=AVERAGEIFS(Assessment!$L$1:$L$100,Assessment!$A$1:$A$100,A1,Assessment!$O$1:$O$100,"Y")

or you can use this array formula on older versions of Excel:

=AVERAGE(IF((Assessment!$A$1:$A$100=A1)*(Assessment!$O$1:$O$100="Y"),Assessment!$L$1:$L$100))

with CSE.

 

[rsutton1981]

Cheers Eric, I am updating my profile.

Thanks