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:Enter the following formula in

`C1`

and ctrl-enter/copy-paste/fill-down/auto-fill into the rest of the table's column: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`}`

):Explanation:The

`SMALL(`

function returns the nth smallest value of the array,array,n). As the default for the third argument of theignoring boolean values`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+:

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:

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