## 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

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

Formula in cell D6:

### 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

Array formula in cell D6:

### 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.

### 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 […]

The image above shows you a formula in cell D3 that tries to get the smallest number from cell range […]

Today I learned how to sort numbers from multiple cell ranges thanks to Sam Miller. It is surprisingly simple and easy. […]

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

This article demonstrates how to sort numbers from small to large using a condition or criteria, I will show how […]

This article demonstrates a formula that sorts text values based on character length, the Excel 365 dynamic array formula is […]

## Functions in this article

More than 1300 Excel formulas

## 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