## Smallest greater than condition

**Question:**

i have a data table with 2 columns:

A B

2.93 12.8

2.94 12.2

3 8.38

3.03 6.76

3.04 5.33

3.06 6.36

Lets say i have a cell with number 3. I need to find a number in column A that has a number >= than 3, but also has the smallest number in column B.

(with my cell = 3 it would be 3.04 from A and 5.33 from B)

Simple vlookup gives me first >= number, but in most cases in column B is not the smallest number.

**Answer:**

The formula extracts the smallest number larger than a given number displayed in cell F2.

Formula in E6:

Formula in cell F6:

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

Array formula in E6:

Array formula in F6:

### Explaining formula in cell E6

#### Step 1 - Construct logical expression

The logical expression is used in the IF function in order to return a given value. The > is a larger than sign.

B3:B8>F2

becomes

{2.93;2.94;3;3.03;3.04;3.06}>3

and returns

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

#### Step 2 - Extract values in array

IF(B3:B8>F2,B3:B8)

becomes

IF(B3:B8>F2,B3:B8)

becomes

IF({FALSE; FALSE; FALSE; TRUE; TRUE; TRUE},{2.93;2.94;3;3.03;3.04;3.06})

and returns

{FALSE; FALSE; FALSE; 3.03; 3.04; 3.06}

#### Step 3 - Find smallest value

The MIN function ignores text, blank and boolean values.

MIN(IF(B3:B8>F2,B3:B8))

becomes

MIN({FALSE; FALSE; FALSE; 3.03; 3.04; 3.06})

and returns 3.03 in cell E6.

Table of Contents Find closest value Find closest values Find closest values and return adjacent values Find closest value with […]

How to quickly find the maximum or minimum value [Formula]

Question: I have three columns and how do I identify the largest and smallest number? Where is the value? I […]

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

The MIN function allows you to retrieve the smallest number in a cell range. The formula in cell D3 extracts […]

### One Response to “Smallest greater than 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!!