2

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.

4
  • 1
    Is your data always going to be sorted?
    – BruceWayne
    Aug 21, 2018 at 13:45
  • I think what you're looking for is the value in column A which is the next value greater than the value you're looking for. If all the values are integers, and you can't sort, you could calculate B+1-A and look for the smallest value >= 0.
    – Jeter-work
    Aug 21, 2018 at 14:26
  • @Xalorous It's a little more complicated than that for an unsorted column A. Finding the smallest value using a lookup function or MATCH() requires the column to be sorted. It can be done, but it you need to use an array formula, the SMALL() function, and some trickery.
    – robinCTS
    Aug 21, 2018 at 14:33
  • Hello all, the columns can be sorted indeed, it is not a requirement for them to stay in the same order. Aug 22, 2018 at 8:30

2 Answers 2

3

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

3
  • Does MATCH() return the cell address or the value?
    – Jeter-work
    Aug 21, 2018 at 14:21
  • 1
    @Xalorous - MATCH() returns a row number. Index returns a value from that row number.
    – BruceWayne
    Aug 21, 2018 at 14:25
  • 1
    @Xalorous MATCH() returns the 1-based index into the range of the second argument. That turns out to be the row number if the range is an entire column.
    – robinCTS
    Aug 21, 2018 at 14:26
0

You can use e.g. the array function {=MIN(IF(A1:A6 > B1; A1:A6))} or {=MIN(IF(A1:A6 > B1; A1:A6; 1000))} (with 1000 as fallback value).

It takes the minimum of all values from column A that are greater than the vaue from the current cell from column B (here B1). Therefore neither of the columns must be sorted.

With Excel >= 2016 you may also use the MINIFS function.

Note that array functions must be inserted by pressing Ctrl+Shift+Enter.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .