Function to find next missing number?

[Rob*]

Is there any function that can return the next missing number from a column with ascending numbers?
The highest number can be anything and there can be several numbers missing after each other.
Also the same number will not appear twice.
I only need to find the next missing number.

Ex:
-| A | B |
1| 1 | (Function to return "3")
2| 2 |
3| 4 |
4| 7 |
5| 8 |
6| 9 |
7|   |
8|
9|

Or if no function works, I can use VBA, but prefer not.

 

[Peter_SSs]

Does the list of numbers always start in cell A5? If so, does this adaptation of Ron's first standard formula do the job?

=IF(A5=1,INDEX(A5:A1005,MATCH(1,INDEX(--((A6:A1005-A5:A1004)<>1),0),0))+1,1)

 

[Rob*]

Try this formula...

=LOOKUP(2,1/(A5:A1005=(ROW(A5:A1005)-ROW(A5)+1)),A5:A1005)+1

Is that something you can work with?

Yay! This seem to work after struggeling with translate it to my language

=LETAUPP(2;1/(A5:A1005=(RAD(A5:A1005)-RAD(A5)+1));A5:A1005)+1

 

[Ron Coderre]

I'm glad I could help.

 

[Peter_SSs]

Nope, 1 might not be present ..

Yay! This seem to work after struggeling with translate it to my language

=LETAUPP(2;1/(A5:A1005=(RAD(A5:A1005)-RAD(A5)+1));A5:A1005)+1

Doesn't that error if 1 is not the first number?

 

[Rick Rothstein]

How about this array-entered** formula...

=MIN(IF(A6:A1006-A5:A1005>1,A5:A1005+1))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[Ron Coderre]

Doesn't that error if 1 is not the first number?

Yes...it would need a "missing 1" trap
Excel 2007 or later

=IFERROR(LOOKUP(2,1/(A5:A1005=(ROW(A5:A1005)-ROW(A5)+1)),A5:A1005)+1,1)

)

Pre-Excel 2007

=IF(A5<>1,1,LOOKUP(2,1/(A5:A1005=(ROW(A5:A1005)-ROW(A5)+1)),A5:A1005)+1)

EDITED TO INCLUDE THIS ALTERNATIVE:
Since we do know where the range starts, I suppose this abbreviated regular formula could be used:

=IF(A5=1,LOOKUP(2,1/(A5:A1005=(ROW(A5:A1005)-4)),A5:A1005)+1,1)

 

[Rick Rothstein]

How about this array-entered** formula...

=MIN(IF(A6:A1006-A5:A1005>1,A5:A1005+1))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Noting Peter's comment on another message... my formula would have trouble if A5 is not a 1, so use this array-entered formula instead...

=IF(A5<>1,1,MIN(IF(A6:A1006-A5:A1005>1,A5:A1005+1)))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[Peter_SSs]

Yes...it would need a "missing 1" trap

As I suggested in post #11.

How about this array-entered** formula...

=MIN(IF(A6:A1006-A5:A1005>1,A5:A1005+1))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

Gives incorrect result if 1 is missing. If A5 down is ..
2,3,7 the result is 4
2,3,4 the result is 0

Also needs a missing 1 trap.

Edit: Ah, Rick I see you got this

 

[Rob*]

Yes...it would need a "missing 1" trap
Excel 2007 or later

=IFERROR(LOOKUP(2,1/(A5:A1005=(ROW(A5:A1005)-ROW(A5)+1)),A5:A1005)+1,1)

)

Pre-Excel 2007

=IF(A5<>1,1,LOOKUP(2,1/(A5:A1005=(ROW(A5:A1005)-ROW(A5)+1)),A5:A1005)+1)

EDITED TO INCLUDE THIS ALTERNATIVE:
Since we do know where the range starts, I suppose this abbreviated regular formula could be used:

=IF(A5=1,LOOKUP(2,1/(A5:A1005=(ROW(A5:A1005)-4)),A5:A1005)+1,1)

True.
I used:

=IFERROR(LOOKUP(2,1/(A5:A1005=(ROW(A5:A1005)-ROW(A5)+1)),A5:A1005)+1,1)

And it does all I need.

Thanks a thousand times to all of you that helped me!