Sorted
The simplest formula is for the case where column A
is sorted in ascending order:
Enter the following formula in C1
and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column:
=INDEX(A:A,1+MATCH(B1,A:A,1))
Explanation:
The 1
as the third argument of MATCH()
means that it finds the largest value that is less than or equal to the first argument. Adding 1
to that index results in the index of the next higher number. The INDEX()
function then extracts the number.
Note that I have added an extra value at the end of column A
. This is for the special case where there is no next higher value.
Unsorted
For the case where column A
is unsorted (also works if sorted), the formula is a little more complicated:
Array enter (Ctrl+Shift+Enter) the following formula in C1
and copy-paste/fill-down into the rest of the table column (don't forget to remove the {
and }
):
{=SMALL(IF($A$1:$A$6>B1,$A$1:$A$6),1)}
Explanation:
The SMALL(array,n)
function returns the nth smallest value of the array, ignoring boolean values. As the default for the third argument of the IF()
function is FALSE
, only values greater than the value in column B
are checked, resulting in the next higher value.
Note that a special terminating value for column A
is not required, as a #NUM!
error is the result if there are no values in column A
greater than the value in column B
.
Finally, as aventurin has pointed out, there is an alternate, similar formula which works irrespective of sorting (but with an important caveat).
For Excel 2016+:
=MINIFS($A$1:$A$6,$A$1:$A$6,">"&B1)
This works because the MINIFS()
function filters out the values that don't match the criteria(s) before extracting the minimum value.
For earlier versions of Excel:
{=MIN(IF($A$1:$A$6>B1,$A$1:$A$6))}
This works for the same reason as the SMALL()
function - it ignores boolean values generated by the IF()
function.
Caveat:
Both the =MINIFS()
and {=MIN(IF())}
formulas won't work correctly if a zero can be the correct next higher value, as zero is also returned when there is no next higher value. (This is the same reason for adding an extra value at the end of column A
for the first formula - that formula also returns a zero if there are no higher values.)
A
. Finding the smallest value using a lookup function orMATCH()
requires the column to be sorted. It can be done, but it you need to use an array formula, theSMALL()
function, and some trickery.