Trying to Convert MIN formula to SMALL to find second smallest

[slam]

Per the title, I'm trying to convert this array formula to find the second smallest value:

=IF(L2=0,"NEVER",MIN(IF($B$4:$B$403=H2,$A$4:$A$403)))

I haven't had any success trying to fit in the SMALL and ,2. Any help would be greatly appreciated. Thanks!

 

[63falcondude]

Try

=IF(L2=0,"NEVER",SMALL(IF($B$4:$B$403=H2,$A$4:$A$403),2)) Ctrl Shift Enter

 

[slam]

Try

=IF(L2=0,"NEVER",SMALL(IF($B$4:$B$403=H2,$A$4:$A$403),2)) Ctrl Shift Enter

Hmm, that's exactly what I tried.... and now it works of course. Maybe I forgot the Ctrl Shift Enter, but I didn't think so. Thank you anyway!

 

[63falcondude]

Hmm, that's exactly what I tried.... and now it works of course. Maybe I forgot the Ctrl Shift Enter, but I didn't think so. Thank you anyway!

Glad to help!

 

[slam]

Actually, a follow up question if I may. If there is no second smallest, i.e. there is only one value meeting the criteria, can I make it not error and display a blank instead?

Thanks

 

[63falcondude]

Actually, a follow up question if I may. If there is no second smallest, i.e. there is only one value meeting the criteria, can I make it not error and display a blank instead?

Thanks

How about using IFERROR?

Formula would become =IFERROR(IF(L2=0,"NEVER",SMALL(IF($B$4:$B$403=H2,$A$4:$A$403),2)),"") Ctrl Shift Enter