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.

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.

Back to top

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

1. Type the formula in cell B3
2. Then press and hold CTRL and SHIFT simultaneously,
3. Now press Enter once.
4. 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.

Back to top

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.

Back to top

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.

Back to top

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

Array formula in cell D6:

How to enter an array formula

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.

Back to top

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

Array formula in cell D6:

How to enter an array formula

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.

Back to top

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

Small category

More than 1300 Excel formulas

Excel categories

Leave a Comment