# Find the smallest number in a list that is larger than a given number

This article demonstrates formulas that lets you extract the smallest number larger than a given number.

The example above specifies the given number in cell D3, in this case, 45. The data is in cell range B3:B16, the formula in cell D6 calculates the smallest value but larger than 45.

### Table of Contents

- Find the smallest number that is larger than a given number - [Excel 2016]
- Find the smallest number that is larger than a given number - earlier Excel versions
- Find the largest number that is smaller than a given number - [Excel 2016]
- Find the largest number that is smaller than a given number - earlier Excel versions
- How to find the k-th smallest number that is larger than a given number?
- How to find the k-th largest number that is smaller than a given number?
- How to find the smallest number excluding zeros
- Get Excel file

## 1. Find the smallest number in a list that is larger than a given number - [Excel 2016]

This example shows an Excel 2016 function that extracts the smallest number larger than a given condition. The condition is specified in cell D3 and the source data is deployed in cell range B3:B16.

Cell D3 contains number 45, the formula in cell D6 returns 50 which is the smallest number larger than 45 in cell range B3:B16.

Formula in cell D6:

Section 2 below describes a formula that works in all Excel versions.

### 1.1 Explaining formula

#### Step 1 - Populate arguments

The MINIFS function calculates the smallest value based on a given set of criteria.

Function syntax: MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

MINIFS(min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

becomes

MINIFS(B3:B16,B3:B16,">"&D3)

The greater than sign > is a logical operator that lets you check if a number is larger than another number.

The ampersand character & lets you concatenate values in an Excel formula. You also have the option to enter the greater than sign in cell D3 combined with the given number, like this: >45. If you do remove the less than sign and the ampersand and reference only cell D3 in the MINIFS function.

#### Step 2 - Evaluate MINIFS function

MINIFS(B3:B16,B3:B16,">"&D3)

becomes

MINIFS({86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88},{86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88},">"&45)

and returns 50.

## 2. Find the smallest number in a list that is larger than a number - earlier Excel versions

This example demonstrates a formula that works in all Excel versions, it extracts the smallest number larger than a given condition.

The condition is specified in cell D3 and the source data is in cell range B3:B16. Cell D3 contains number 45, the formula in cell D6 returns 50 which is the smallest number larger than 45 in cell range B3:B16.

Array formula in cell D6:

This formula is an array formula, it is required to enter this formula given the instructions in section 2.1 below if you work in an Excel version earlier than Excel 365.

### 2.1 How to enter an array formula

- Type the formula in cell B3
- Then press and hold CTRL and SHIFT simultaneously,
- Now press Enter once.
- Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully.

Don't enter the curly brackets yourself, they appear automatically.

### Explaining array formula

#### Step 1 - Filter values in cell range larger than the conditionÂ in cellÂ D3

The IF function allows you to create a logical expression that evaluates to TRUE or FALSE. You can then choose what will happen to a value that returns TRUE and also FALSE.

IF(*logical_test*, [*value_if_true*], [*value_if_false*])

If you use a cell range instead of a cell reference to a single cell you evaluate all the values in the cell range. The larger than sign lets you check if the values in B3:B16 areÂ larger than the value in D3.

IF(B3:B16>D3,B3:B16)

becomes

IF({86;51;68;50;38;66;23;59;79;72;19;99;76;88}>45,B3:B16)

becomes

IF({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE},B3:B16)

becomes

IF({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE},{86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88})

and returns the following array:

{86;51;68;50;FALSE;66;FALSE;59;79;72;FALSE;99;76;88}

#### Step 2 - Return the smallest value in the array

The MIN function ignores boolean values which is useful in this case.

MIN(IF(B3:B16>D3,B3:B16))

becomes

MIN({86;51;68;50;FALSE;66;FALSE;59;79;72;FALSE;99;76;88})

and returns 50 in cell D6.

## 3. Find the largest number in a list that is smaller than a given number - [Excel 2016]

The image above shows a small formula in cell D6 that calculates the largest number in cell range B3:B16 smaller than the condition specified in cell D3.

The formula in cell D6 returns 38, it is smaller than 45 but the largest of the numbers smaller than 45.

Excel 2016 formula in cell D6:

Section 4 describes a formula for earlier Excel versions than Excel 2016.

### 3.1 Explaining formula

#### Step 1 - Populate arguments

The MAXIFS function calculates the highest value based on a condition or criteria.

Function syntax: MAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

MIAXIFS(max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

becomes

MAXIFS(B3:B16,B3:B16,"<"&D3)

The less than sign < is a logical operator that lets you check if a number is smaller than another number. The ampersand character & lets you concatenate values in an Excel formula.

#### Step 2 - Evaluate MINIFS function

MINIFS(B3:B16,B3:B16,">"&D3)

becomes

MINIFS({86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88},{86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88},"<"&45)

and returns 38.

## 4. Find the largest number that is smaller than a given number - earlier Excel versions

Array formula in cell D6:

### 4.1 Explaining the formula

#### Step 1 - Filter values in cell range larger than the conditionÂ in cellÂ D3

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(B3:B16>D3,B3:B16)

becomes

IF({86;51;68;50;38;66;23;59;79;72;19;99;76;88}<45,B3:B16)

becomes

IF({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE},B3:B16)

becomes

IF({FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE},{86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88})

and returns the following array:

{FALSE;FALSE;FALSE;FALSE;38;FALSE;23;FALSE;FALSE;FALSE;19;FALSE;FALSE;FALSE}

#### Step 2 - Return the smallest value in the array

The MAX function calculate the largest number in a cell range.

Function syntax: MAX(number1, [number2], ...)

MAX(IF(B3:B16>D3,B3:B16))

becomes

MAX({FALSE;FALSE;FALSE;FALSE;38;FALSE;23;FALSE;FALSE;FALSE;19;FALSE;FALSE;FALSE})

and returns 38 in cell D6.

## 5. How to find the k-th smallest number that is larger than a given number?

Array formula in cell D6:

### 5.1 Explaining formula

#### Step 1 - Check if numbers are larger than the condition

The larger than character lets you preform logic in an Excel formula. The result is a boolean value TRUE or FALSE.

B3:B16>D3

becomes

{86;51;68;50;38;66;23;59;79;72;19;99;76;88}>45

and returns

{TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}.

#### Step 2 - Extract numbers larger than the condition in cell D3

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(B3:B16>D3,B3:B16,"")

becomes

IF({TRUE; TRUE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE},{86;51;68;50;38;66;23;59;79;72;19;99;76;88},"")

and returns

{86; 51; 68; 50; ""; 66; ""; 59; 79; 72; ""; 99; 76; 88}

#### Step 3 - Extract the k-th smallest number

The SMALL function returns the k-th smallest value from a group of numbers.

Function syntax: SMALL(array, k)

SMALL(IF(B3:B16>D3,B3:B16,""),D6)

becomes

SMALL({86; 51; 68; 50; ""; 66; ""; 59; 79; 72; ""; 99; 76; 88},D6)

becomes

SMALL({86; 51; 68; 50; ""; 66; ""; 59; 79; 72; ""; 99; 76; 88},2)

and returns 51.

50 is the smallest number in the array, however, we are looking for the second smallest number in the array.

## 6. How to find the k-th largest number that is smaller than a given number?

Array formula in cell D6:

### 6.1 Explaining formula

#### Step 1 - check if numbers are smaller than the condition

B3:B16<D3

becomes

{86;51;68;50;38;66;23;59;79;72;19;99;76;88}<45

and returns

{FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE}.

#### Step 2 - extract numbers smaller than the condition in cell D3

The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Function syntax: IF(logical_test, [value_if_true], [value_if_false])

IF(B3:B16<D3,B3:B16,"")

becomes

IF({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE},B3:B16,"")

becomes

IF({FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE},{86; 51; 68; 50; 38; 66; 23; 59; 79; 72; 19; 99; 76; 88}, "")

and returns

{""; ""; ""; ""; 38; ""; 23; ""; ""; ""; 19; ""; ""; ""}.

#### Step 3 - extract the k-th largest number

The LARGE function calculates the k-thÂ largest value from an array of numbers.

Function syntax: LARGE(array, k)

LARGE(IF(B3:B16<D3,B3:B16,""),D6)

becomes

LARGE({""; ""; ""; ""; 38; ""; 23; ""; ""; ""; 19; ""; ""; ""},D6)

becomes

LARGE({""; ""; ""; ""; 38; ""; 23; ""; ""; ""; 19; ""; ""; ""},2)

and returns 23.

38 is the largest number in the array, however, we need to extract the second largest number specified in cell D6.

## 7. How to find the smallest number excluding zeros

Column B contains numbers, the formula in cell D3 calculates the smallest value excluding zeros. Note that all numbers are either 0 (zero) or larger.

Formula in cell D3:

The MINIFS function appeared first in Excel 2016 and is a fairly new function. Here is the Excel function syntax:

MINIFS(*min_range*,Â *criteria_range1*,Â *criteria1*, [*criteria_range2*,Â *criteria2*], ...)

We only have one condition so the function syntax becomes:

MINIFS(*min_range*,Â *criteria_range*,Â *criteria*)

The *min_range* argument points to the cells containing the numbers B3:B14.

TheÂ *criteria_rangeÂ *is the cell range to be evaluated B3:B14.

*criteria* isÂ the condition,Â "<>0". <> means not equal to.

If cell in B3:B14Â is not equal to 0 (zero) then return cell in B3:B14Â and lastly find the smallest value.

Use the following array formula if you don't have access to the MINIFS function.

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining formula in cell D3

#### Step 1 - Build logical expression

The IF function requires a logical expression in the first argument in order to return a given value when the logical expression evaluates to TRUE and another value when FALSE.

becomes

{100; 50; 40; 0; 60; 35; 190; 80; 120; 165; 0; 20}<>0

and returns

{TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE}.

I have entered the array in column A, note that boolean value FALSEÂ corresponds to cells containing 0 (zero).

#### Step 2 - Filter values in array

The picture shows the array calculated by the IF function, each 0 (zero) is replaced with boolean value FALSE.

becomes

IF({TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE}, B3:B14)

becomes

IF({TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; TRUE}, {100; 50; 40; 0; 60; 35; 190; 80; 120; 165; 0; 20})

and returns

{100; 50; 40; FALSE; 60; 35; 190; 80; 120; 165; FALSE; 20}

#### Step 3 - Find smallest value

The MIN function ignores text, blank and boolean values.

MIN(IF(B3:B14<>0,B3:B14))

becomes

MIN({100; 50; 40; FALSE; 60; 35; 190; 80; 120; 165; FALSE; 20})

and returns 20 in cell D3.

### Useful links

How to find smallest positive value (greater than 0) in Excel?

Calculate the smallest or largest number in a range

Minimum Value in a Range if Greater Than "X"

Get the smallest number larger than a given number and a condition

Get the smallest number larger than a given number and criteria

### If category

I have demonstrated in a previous postÂ how to simplify nested IF functions, in this article I will show you how […]

You don't need to use the IF function in a SUMPRODUCT function, it is enough to use a logical expression. […]

### Small category

This article demonstrates ways to sort numbers from smallest to largest ignoring duplicate numbers. Table of Contents SMALL function with […]

This article demonstrates how to extract multiple numbers based on a condition and return a sorted list from small to […]

This article shows how to create a formula that sorts numbers from small to large excluding zeros. I will also […]

### Excel categories

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form