# Trying to Convert MIN formula to SMALL to find second smallest

#### slam

##### Well-known Member
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!

### Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Try

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

Last edited:
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!

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!

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

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

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

Replies
3
Views
238
Replies
2
Views
720
Replies
7
Views
173
Replies
3
Views
163
Replies
3
Views
163

1,211,839
Messages
6,104,287
Members
447,901
Latest member
boy3hc2004

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back