modify formula to find second smallest number

Hi,
I would like someone to modify the below formula to arrive at the second smallest number.

MIN(IF($EH$14:$EH$105462=EH15,$DH$14:$DH$105462)))

Many thanks

Ian
raceproretiredAsked:
Who is Participating?
 
Ejgil HedegaardCommented:
Agree with Martin that the small function can do it, but there is a condition on another column, so try this
=SMALL(IF($EH$14:$EH$105462=EH15,$DH$14:$DH$105462,10^10),2)
It is an array formula, inserted with Ctrl+Shift+Enter
1
 
Martin LissOlder than dirtCommented:
Try using the small function where 2 is the second smallest. This looks at the data in A1 and B1and if they are equal it returns the second smallest from A1:A6, otherwise it returns 99.

=IF(A1=B1,SMALL(A1:A6,2),99)
1
 
raceproretiredAuthor Commented:
Thanks to Martin and Ejgil for your contribution.
Your solution Ejgil is perfect
Many thanks
Ian
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.