Author: Oscar Cronquist Article last updated on November 13, 2018

Question:

Here is the problem:
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:

=MINIFS(B3:B8,B3:B8,">"&F2)

Formula in cell F6:

=MINIFS(C3:C8,C3:C8,">"&F2)

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:

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

Array formula in F6:

=MIN(IF(C3:C8>F2,C3:C8))

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.

Related articles