Max If formula other than Array Function

[scorpene]

Hello All,

I am trying to extract maximum values in a given set of data. I am currently using an array function =MAX(IF($A$2:$A$12=B2,$C$2:$C$12)) ). Every time i need to press Ctrl+Shift+Enter.

Is there anyway that I can get the maximum values out of a given data set without using an array function. I have attached screenshot for ready reference.

Thanks !!

 

[Fluff]

How about
=AGGREGATE(14,6,$C$2:$C$12/($A$2:$A$12=B2),1)

 

[scorpene]

Thank you so much @Fluff .

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Peter_SSs]

@scorpene
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

For example, if you have Excel 365 with the FILTER function you could also use
=MAX(FILTER(C$2:C$12,A$2:A$12=B2,0))

Also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.