Excel SMALL() Function

The SMALL() function in excel returns the k-th smallest value in a data set.


SMALL(Array, kth small number from the list)


Array (required)- This parameter represents the list of numbers from which nth smallest number needs to be found out.

K(required)- This parameter represents the kth smallest number which needs to be found out like 5th smallest number etc.




This function returns the k-th smallest number from the given set of values.

Example 1

Objective: Use SMALL function to find the nth smallest value from the set of values

Array/List of Numbers kth smallest number Result Formula
3 1 5 1 -8.5  =SMALL(A18:C21,D18)
19 50 1 5 3  =SMALL(A18:C21,D19)
8 12 20 4 1  =SMALL(A18:C21,D20)
-8.5 3 0.5 12 50  =SMALL(A18:C21,D21)

Example 2

Emp_ID Employee Name DOJ Age Department Salary
TC-001 Sukla Rathore 21-Mar-16 21 Engineer $95,000
TC-002 Shivam Ghandhi 27-Feb-16 39 Sales Executive $150,000
TC-003 Thomas Mitchell 11-Jan-16 40 Engineer $115,550
TC-004 Ashish Kumar Patra 16-Feb-16 19 Engineer $99,500
TC-005 Chonmila Vualnam 31-Mar-15 28 Customer Support Executive $35,000

Objective: Find out the age of 3rd youngest employee from the above table

Result Formula
28   =SMALL(E60:E64,3)