It's not so hard to get the maximum or minimum of a range based on criteria, but how about the 3rd largest (highest) number of a range based on criteria?

One way is to use a helper column. Let's assume you have names in Column A and numbers in Column B. The name you want to match is in A1.

In C1 enter =IF(A1=$A$1,B1,"") and drag down. In another cell such as D1 enter =LARGE(C:C,3)

Change the 3 in the LARGE formula to suit.

or

Enter the below formula as an array A1 contains criteria (Push Ctrl, Shift and Enter simultaneously)

A1:A100 contains criteria to be matched and B1:B100 is the range to find the 3rd largest value

{=LARGE(IF(A1:A100=A1,B1:B100),3)}

© Copyright
andrewsexceltips.net All Rights Reserved.