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.

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles