# Next Highest Number

#### Brabed

##### Board Regular
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?

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### barry houdini

##### MrExcel MVP
Try

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

Perfect! THANKS

Replies
5
Views
393
Replies
0
Views
246
Replies
9
Views
445
Replies
0
Views
1K
Replies
2
Views
533

1,190,773
Messages
5,982,835
Members
439,799
Latest member
matts12

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back