Find 3rd Largest Using Criteria
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)}