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.

 

[Ron Coderre]

Using this example

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

This regular formula returns the first missing number in the sequence

B1: =INDEX(A:A,MATCH(1,INDEX(--((A2:A20-A1:A19)<>1),0),0))+1

In that example, that formula returns: 3

Is that something you can work with?

 

[T. Valko]

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.

Is the range of numbers always from 1 to N?

Will 1 always be present?

 

[Rob*]

Is the range of numbers always from 1 to N?

Will 1 always be present?

Nope, 1 might not be present and the highest number is unknown (but never seen and higher than 1000). If 1 is not present then 1 should be next missing number.

 

[Rob*]

Oh! I also forgot the numbers starts from A5 and down not from A1 as my example. I have some text and headers A1 to D4

 

[T. Valko]

Oh! I also forgot the numbers starts from A5 and down not from A1 as my example. I have some text and headers A1 to D4

Ok, does that mean the sequence starts from 1 although 1 may not be present?

We need to establish a starting point for the missing number(s)!

 

[Rob*]

This regular formula returns the first missing number in the sequence

B1: =INDEX(A:A,MATCH(1,INDEX(--((A2:A20-A1:A19)<>1),0),0))+1

In that example, that formula returns: 3

Is that something you can work with?

This seem just to return the lowest value +1. = value 2 if my lowest value is 1.

 

[Rob*]

Ok, does that mean the sequence starts from 1 although 1 may not be present?
Yes

We need to establish a starting point for the missing number(s)!
Hmm, to do some sort of range we can check for next missing number in the range of 1 to 10000.

 

[Ron Coderre]

Ok, does that mean the sequence starts from 1 although 1 may not be present?
Yes

We need to establish a starting point for the missing number(s)!
Hmm, to do some sort of range we can check for next missing number in the range of 1 to 10000.

Try this formula...

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

Is that something you can work with?

 

[T. Valko]

Ok, does that mean the sequence starts from 1 although 1 may not be present?
Yes

We need to establish a starting point for the missing number(s)!
Hmm, to do some sort of range we can check for next missing number in the range of 1 to 10000.

Try this...

Book1
	A	B
2	1	3
3	2	_
4	4	_
5	7	_
6	8	_
7	9	_
Sheet1

Create this defined name formula:

Name: Nums
Refers to: =ROW(INDIRECT("1:10000"))

Then, enter this array formula** in B2:

=SMALL(IF(ISNA(MATCH(Nums,A$2:A$7,0)),Nums),ROWS(B$2:B2))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.