Excel – Finding the next higher value in a column

lookupmicrosoft excelworksheet-function

I have two columns, A and B. Column B has values which I need to look up in Column A. However, I don't need to find the exact corresponding value, I need the next higher value.

For example:

Column A    Column B   

   2           3
   4           4
   5           5
   7           6
   8           8
   9           9

So, for the value 5 in column B, I want to return 7 from column A.

I guess I probably need some form of lookup / index-match function but I haven't been able to write the formula myself.

Best Answer

Sorted

The simplest formula is for the case where column A is sorted in ascending order:

Worksheet Screenshot

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.)