Author: Oscar Cronquist Article last updated on November 21, 2017


Here is the problem:
i have a data table with 2 columns:
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.


Array formula in E4:

=INDEX(A1:A6, MATCH(MIN(IF(A1:A6>=F1, B1:B6)), B1:B6, 0)) + CTRL + SHIFT + ENTER

Array formula in E5:

=MIN(IF(A1:A6>=F1, B1:B6)) + CTRL + SHIFT + ENTER

Functions in this article:

IF(logical_test, [value_if_true], [value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Returns the smallest number in a set of values. Ignores logical values and text

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value