Next Highest Number

[Brabed]

I have a column of numbers that are consecutive for the most part but every now and then a number is skipped, as in:


A

3
4
5
6
9
10
12
13
16


Now let's say I have a cell value in C1 of 6. I want to return a corresponding value in column B (column B is omitted from this post) for a reference to column A like this :

lookup(C1+1,A:A,B:B)

Since there is no 7, the formula would return the corresponding value to 6, but I want the corresponding value to 9. How do I get the formula to recognize skipped numbers. Is there any other way to reference column A other then C1+1 that would solve this?

 

[barry houdini]

Try

=INDEX(B:B,MATCH(C1+1,A:A)+(LOOKUP(C1+1,A:A)<>C1+1))

 

[Brabed]

Perfect! THANKS