Finding the Nth largest in non-contiguous range

[medelste]

I have hundreds of rows of data coming in regularly and need an answer too quickly to sort the data first. Can you assist me in coming up with a formula that finds the nth largest number in a range based on criteria in another column? For example, how would I get the the second largest value in the second column only for those rows that contain the word "Apple" in the first column (answer: 7)? I have Excel 365 but may have some colleagues that have older version of Excel.

Apple	5​
Banana	3​
Cherry	8​
Orange	5​
Blueberry	0​
Banana	2​
Orange	6​
Apple	9​
Orange	1​
Apple	7​
Cherry	1​
Cherry	8​
Blueberry	5​
Apple	1​

 

[BSALV]

in a 2013
-9E99 is a very large negative number, so if you get that, there are no other "apples" found.

Map1
	A	B	C	D
1	Apple	1		27
2	5	2		19
3	Banana	3		15
4	3	4		1
5	Cherry	5		-9E+99
6	8	6		-9E+99
7	Orange	7		-9E+99
8	5	8		-9E+99
9	Blueberry	9		-9E+99
10	0	10		-9E+99
11	Banana	11		-9E+99
12	2	12		-9E+99
13	Orange	13		-9E+99
14	6	14		-9E+99
15	Apple	15		-9E+99
16	9	16		-9E+99
17	Orange	17		-9E+99
18	1	18		-9E+99
19	Apple	19		-9E+99
20	7	20		-9E+99
21	Cherry	21		-9E+99
22	1	22		-9E+99
23	Cherry	23		-9E+99
24	8	24		-9E+99
25	Blueberry	25
26	5	26
27	Apple	27
28	1	28
Blad1

Cell Formulas
Range	Formula
D1:D24	D1	=LARGE(IF($A$1:$A$28="apple",$B$1:$B$28,-9E+99),ROW())
B1:B28	B1	=ROW()

 

[bebo021999]

Book1
	A	B
1	Apple	5
2	Banana	3
3	Cherry	8
4	Orange	5
5	Blueberry	0
6	Banana	2
7	Orange	6
8	Apple	9
9	Orange	1
10	Apple	7
11	Cherry	1
12	Cherry	8
13	Blueberry	5
14	Apple	1
15
16		7
Sheet1

Cell Formulas
Range	Formula
B16	B16	=AGGREGATE(14,6,$B$1:$B$14/($A$1:$A$14="Apple"),2)

 

[medelste]

Thank you both! I tried the AGGREGATE method first and it works so I'm going with it. I appreciate your time and thought on this!