How to get smallest, 2nd smallest value and 3rd smallest value

Jing Guan Tee

Hello,

I need to run a formula which return smallest, 2nd and 3rd smallest value for a criteria. Example like below, when I key in "Banana" in cell D1, the result in F2 to F4 will give me 100, 150 and 200. This formula does not work because Smartsheet do not support array formula. Anyone have alternative way to make it happen?

L_123

=small(collect(qty:qty,products:products,"Banana"),1)

=small(collect(qty:qty,products:products,"Banana"),2)

=small(collect(qty:qty,products:products,"Banana"),3)

L_123

=small(collect(qty:qty,products:products,"Banana"),1)

=small(collect(qty:qty,products:products,"Banana"),2)

=small(collect(qty:qty,products:products,"Banana"),3)

Jing Guan Tee

https://community.smartsheet.com/discussion/comment/269134#Comment_269134

Hi, it works. Thanks so much for your valuable answer.