## SMALL function and LARGE function

Returns the k-th smallest value in a data set.

**LARGE(***array*, *k***)**

Returns the k-th largest value in a data set.

**Arguments**

array - Is a reference to a cell range or is an array of constants.

k - The position in the array of the value to return.

### Example 1 - Cell range contains numbers, text and blanks

becomes

=SMALL({3; 6; "A"; 5; "X"; 0; 4; 4},3)

Text strings and blanks are overlooked. The array becomes

=SMALL({3; 6; ; 5; ; ; 4; 4},3)

and returns 4. 4 is the third smallest numerical value in the array.

### Example 2 - Use a condition (Array formula)

The following array formula looks for the second smallest value in column C if corresponding value in adjacent column D is 2012-Dec:

becomes

=SMALL(IF({"2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Dec"; "2012-Nov"; "2013-Jan"; "2013-Jan"; "2012-Dec"; "2013-Jan"; "2012-Nov"; "2012-Dec"}="2012-Dec", C4:C14, ""), F4)

becomes

=SMALL(IF({FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE}, C4:C14, ""), F4)

becomes

=SMALL({"";60.69;"";8.08;"";"";"";24.44;"";"";2.25}, 2)

and returns 8.08 in cell F7.

### Download excel *.xlsx file

### Category: Excel

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]Comments(249) Filed in category: Concatenate, Excel, Textjoin

Comments(161) Filed in category: Charts, Excel, Interactive

### 12 Responses to “SMALL function and LARGE function”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Hi Oscar,

I am in love with.. your formula explanation.. :)

Waiting eagerly for MMULT & some D-Functions..

Regards,

Deb

Debraj Roy,

Thank you!

I am curious, in what situation do you use MMULT?

Hi Oscar,

We can use MMULT in all cases where SUMPRODUCT fails..

with only Two Criteria..

* Only TWO Array can be multiplied..

* 1st Array's No Of Row.. Should be Same as 2nd Array's No Of Column..

Unlike SUMPRODUCT, It returns ARRAY output..

I think, Binary Addition & Binary Multiplication are the base of all FORMULA's & FUNCTION..

and you are doing a great job, by teaching/using them in your daily blog..

Regards!

Deb

Debraj Roy,

Well, I am learning from you right now.

Can you provide an example where SUMPRODUCT fails and MMULT succeeds?

I searched and found my old mathematics books from college, I had forgotten the basics of multiplying two matrices. :-)

It is worthwhile mentioning that in both Small and Large K could also be an array

So if A1:A10 contains random numbers the below formulas

=Large(A1:A10,{1,2,3}) - Return an array containing the top 3 numbers

=SUM(Large(A1:A10,{1,2,3}) -Array entered Returns the Sum of the top 3 numbers

=SUM(LARGE(A1:A10,ROW(INDIRECT("1:"&TopN))))- Array Entered Returns the sum of the Top N numbers as defined in the Cell/Named Constant TopN

=Large(A1:A10,Row(A1:A10))- Array entered returns an array of numbers in A1:A10 in Descending order

Likewise Small

sam,

It is worthwhile mentioning that in both Small and Large K could also be an arrayYes you are right! Thanks for pointing that out.

[…] SMALL(array,k) Returns the k-th smallest number in this data set. […]

=Small({VALUE(DV147),VALUE(DZ147),VALUE(ED147),VALUE(EH147)},2) will not work. If I use sum and the "Value(-----)" amounts, it works.

What am I doing wrong?

The numbers are stored as text in those cells for other reasons.

[…] LARGE function extracts the n-th largest number twice for every two cells. This allows us to return both the […]

I'm using the SMALL function inside an array. I understand how to use the function to return an array where values are greater than or equal to a number. But how do I use the function if I want to return results that are between two numbers?

I've tried nesting an AND statement within the IF statement, but it isn't working (no values are returned).

Any suggestions? Thanks!

julie,

=SMALL(IF(($A$2:$A$10< $F$2)*($A$2:$A$10>$F$3),$A$2:$A$10,""),ROW(A1))

[…] LARGE(array,k) returns the k-th largest row number in this data set. […]