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

This article demonstrates how to extract the largest number smaller than a given number based on a condition and criteria.

#### Table of Contents

## 1. Get the smallest number larger than a given number and a condition

Formula in E6:

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

Formula in cell F6:

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

min_range - Required. A reference to the numbers.

criteria_range1 *- *Â A reference to cells to evaluate based on the criteria.

criteria1 - Criteria in the form of a number, expression, or text.

MINIFS function was introduced in Excel 2016, you can use the following formulas if you don't have access to the function.

## 2. Get the smallest number larger than a given number in a date range

Formula in cell F10:

### 2.1 Explaining formula in cell F10

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

#### Step 1 - First condition

The first *criteria_range1* is C5:C10, the first *criteria1 *is ">"&G6.

The ampersand character concatenates the larger than character and the condition specified in cell G6.

C5:C10,">"&G6

The first criteria pair checks if the values in C5:C10 are larger than the value in cell G6.

The image above highlights which cells contain a number larger than the condition.

#### Step 2 - Second condition

The second *criteria_range2* is B5:B10, the second *criteria2 *is ">="&G4.

The ampersand character concatenates the larger than character, the equal sign, and the condition specified in cell G4.

B5:B10,">="&G4

The second criteria pair checks if the dates in B5:B10 are later or equal to the date in cell G4.

The image above shows that all dates are equal or later than the start range date specified in cell G4.

#### Step 3 - Third condition

The third *criteria_range2* is again B5:B10, the third *criteria2 *is "<="&G5.

The ampersand character concatenates the less than character, the equal sign, and the condition specified in cell G4.

B5:B10,"<="&G5

The second criteria pair checks if the dates in B5:B10 are later or equal to the date in cell G4.

The image above highlights dates equal to or earlier than the start range date specified in cell G4.

#### Step 4 - Evaluate MINIFS function

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

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

MINIFS(C5:C10,C5:C10,">"&G6,B5:B10,">="&G4,B5:B10,"<="&G5)

returns 3.04 in cell F10. When all conditions are met value 3.04 is returned.

#### Formula in cell G10

MINIFS(D5:D10,D5:D10,">"&G6,B5:B10,">="&G4,B5:B10,"<="&G5)

returns 4.2. Values 4.2 and 5.33 meet all conditions, however, 4.2 is the smallest.

## Get Excel *.xlsx file

### Useful links

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?

Combine Text from Multiple Cells - Contextures

Using calculation operators in Excel formulas

More than 1300 Excel formulas### Excel categories

### One Response to “Get the smallest number larger than a given number and a condition”

### Leave a Reply

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

Hi!

Could I please request a tutorial on how to get multiple results based on multiple criteria from different columns? An example of the problem is below,

Edit to hopefully be clearer

S/N...Sweet.....Sour.....Bitter...Type

1.....Sweet..............Bitter....A

2.............. Sour...............B

3.....Sweet.....Sour...............C

4........................Bitter....A

5..............Sour................B

The criteria for input will be Sweet or Sour or Bitter and Type.

For example, if input is Sweet and A, then the results will be 1, if input is Bitter and A, then the results will be 1 and 4.

I've been stuck at this for days....could you please help?

Thank you so much!!