LARGE Function with Conditions

[andrewc]

Hi,

I have data arranged in columns A and B in the attached file and would like to create a formula in cell D2 that will return the largest (and the 2nd, 3rd etc largest) values from column B that are associated with AAA, CCC or DDD.

I've had a go producing at the formula but it doesn't seem to work - I would expect the answer to be 800 for the largest value and the 2nd largest would be 700.

Can someone please suggest how to fix my formula.

Thanks!

[tim201110]

=large(if(isnumber(find($b$2:$b$11,"aaacccddd")),$a$2:$a$11),1)

[andrewc]

Thanks for the quick reply

Unfortunately this seems to return 0 rather than the number that I expected

[tim201110]

it is strange

[Phuocam]

Try:

=LARGE(IF(($B$2:$B$11="AAA")+($B$2:$B$11="CCC")+($B$2:$B$11="DDD"),$A$2:$A$11),1)

[FlameRetired]

This also will work in your version of Excel. It does not have to be array entered. Just commit with 'Enter' and fill down.

=IFERROR(AGGREGATE(14,6,$A$2:$A$11/(($B$2:$B$11="AAA")+($B$2:$B$11="CCC")+($B$2:$B$11="DDD")),ROWS(C$2:C2)),"")

[protonLeah]

#2 works when "UPPER CASE" vs lower.