find nth smallest value with multiple criteria

[aghaffar82]

Hello everyone, I am stuck to find nth smallest value from a range of cells with multiple criteria. I have a formula to extract nth largest values but not being able to reverse that formula to get the nth smallest value.

The formula i'm using to extract nth largest value is as follows:
=SUMPRODUCT(LARGE(($I$2:$I$11>=A17)*($I$2:$I$11<=B17)*($L$2:$L$11),2))

I need to get nth smallest value for the exact same ranges.

Kindly help. Best Regards

 

[FormR]

Hi, welcome to the forum!

Here are a couple of alternatives:

For nth largest:
=AGGREGATE(14,6,$L$2:$L$10/(($I$2:$I$10>=$A$17)*($I$2:$I$10<=$B$17)),2)

And nth smallest:
=AGGREGATE(15,6,$L$2:$L$10/(($I$2:$I$10>=$A$17)*($I$2:$I$10<=$B$17)),2)

 

[aghaffar82]

Hi, welcome to the forum!

Here are a couple of alternatives:

For nth largest:
=AGGREGATE(14,6,$L$2:$L$10/(($I$2:$I$10>=$A$17)*($I$2:$I$10<=$B$17)),2)

And nth smallest:
=AGGREGATE(15,6,$L$2:$L$10/(($I$2:$I$10>=$A$17)*($I$2:$I$10<=$B$17)),2)

Thank you for the welcome and your response. It worked

Much love and respect for quick and accurate help.
Best Regards