Find the 2nd largest value in a range that includes duplicates if the condition in another range is met

[treyabrown]

I have a formula to find 2nd largest value in a range with dulicates

=LARGE(IF(rangeB<LARGE(B2:B500,1),B2:B500),1)> < LARGE(</LARGE(B2:B500,1),B2:B500),1)>rangeB,1),rangeB),1)

I also have another formula to find the second largest value given the condition that the

=LARGE(IF(rangeA="cell value",rangeB),2)

These 2 formulas do what I need but I do not know how to combine them to seach for a portion of the range (based on another range condition) and return the 2nd largest value (given the values are duplicated).I appreciate any help on this, Thanks.<LARGE(B2:B500,1),B2:B500),1)>

</PRE></LARGE(B2:B500,1),B2:B500),1)>

 

[Aladin Akyurek]

Let A:B house the relevant data wth headers in the first row.

E1: KAD (a condition of interest)

E2: Distinct largest values

E3, control+shift+enter, not just enter, and copy down:

=IF(ROWS($E$3:E3)<=2,LARGE(IF($A$2:$A$100=$E$1,
  IF(1-ISNUMBER(MATCH($B$2:$B$100,$E$2:E2,0)),$B$2:$B$100)),
  ROWS($E$3:E3)),"")

 

[treyabrown]

That worked, thanks for the help!

 

[Aladin Akyurek]

That worked, thanks for the help!

You are welcome.