Finding the second lowest value

I have this formula-

=IF(C2="","",MINIFS($F$2:$F$10000,$O$2:$O$10000,O2))

This works fine and gives me the lowest value from range F2:F10

How could I change this formula so that it returns the second lowest value from the same range?

Thanks

Try Array formula

Formula:

=IF(C2="","",SMALL(IF($O$2:$O$10000=O2,$F$2:$F$10000),2))

Thanks for your reply.

I have attached a book as it didn't work for me.

The original formula is in R2, I then wanted to drag the cell down to give the results down column R.

Can you take a look wk9128?

@parbynat

Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula

Many Thanks to you, that made it work.

Non Array formula

Formula:

=IF(C2="","",AGGREGATE(15,6,$F$2:$F$10000/($O$2:$O$10000=O2),2))

You're Welcome. Glad to help . Thank You for the feedback

If you finally get a solution please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".
- Please click on the *Add Reputation button at the bottom left.