The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower boundary is specified in cell E2 and the upper boundary is in cell E3.  The ROW function has a limit of 1 048 576 so the number of values between the lower and upper boundary can't be more than 1 048 576.

If this limit won't work for you then later in this article you will find a macro that doesn't have this limit.

Array Formula in D6

How to create an array formula

1. Select cell D6
2. Copy / Paste array formula
3. Press and hold Ctrl + Shift
4. Press Enter
5. Release all keys

How to copy array formula

1. Copy cell D6
2. Paste to cells below as far as needed.

Explaining formula in cell B5

Step 1 - Build cell reference

The OFFSET function lets you create a cell reference with the same size as there are numbers between 2000000 and 2000010.

OFFSET($B$2,0,0,$E$3-$E$2+1)

becomes

OFFSET($B$2,0,0,2000010-2000000+1)

becomes

OFFSET($B$2,0,0,11)

and returns $B$2:$B$12

Step 2 - Convert cell reference to row numbers

The ROW function creates an array of row numbers based on the cell reference.

ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2

becomes

ROW($B$2:$B$12)-2

becomes

{2;3;4;5;6;7;8;9;10;11;12}-2

and returns

{0;1;2;3;4;5;6;7;8;9;10}

Step 3 - Add start number to array

$E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2

becomes

$E$2+{0;1;2;3;4;5;6;7;8;9;10}

becomes

2000000+{0;1;2;3;4;5;6;7;8;9;10}

and returns

{2000000;2000001;2000002;2000003;2000004;2000005;2000006;2000007;2000008;2000009;2000010}

This is the list we need to figure out which values are missing.

Step 4 - Which values exist?

The MATCH function finds the relative position of each number in the array in cell range $B$3:$B$7, it returns an error if not found.

MATCH($E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2,$B$3:$B$7,0)

becomes

MATCH({2000000;2000001;2000002;2000003;2000004;2000005;2000006;2000007;2000008;2000009;2000010},$B$3:$B$7,0)

becomes

MATCH({2000000;2000001;2000002;2000003;2000004;2000005;2000006;2000007;2000008;2000009;2000010},{2000001;2000003;2000004;2000007;2000008},0)

and returns

{#N/A;1;#N/A;2;3;#N/A;#N/A;4;5;#N/A;#N/A}

Step 5 - Identify errors

The ISERROR function returns TRUE if value is an error and FALSE if not.

ISERROR(MATCH($E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2,$B$3:$B$7,0))

becomes

ISERROR({#N/A;1;#N/A;2;3;#N/A;#N/A;4;5;#N/A;#N/A})

and returns

{TRUE;FALSE; TRUE;FALSE; FALSE;TRUE; TRUE;FALSE; FALSE;TRUE; TRUE}.

Step 6 - Replace errors with numbers

The IF function converts TRUE to corresponding number.

IF(ISERROR(MATCH($E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2,$B$3:$B$7,0)),$E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2)

becomes

IF({TRUE;FALSE; TRUE;FALSE; FALSE;TRUE; TRUE;FALSE; FALSE;TRUE; TRUE},$E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2)

becomes

IF({TRUE;FALSE; TRUE;FALSE; FALSE;TRUE; TRUE;FALSE; FALSE;TRUE; TRUE},{2000000;2000001;2000002;2000003;2000004;2000005;2000006;2000007;2000008;2000009;2000010})

and returns

{2000000; FALSE; 2000002; FALSE; FALSE; 2000005; 2000006; FALSE; FALSE; 2000009; 2000010}

Step 7 - Extract k-th smallest number

The SMALL function extracts the k-th smallest number, this makes the formula return a value in a cell each.

SMALL(IF(ISERROR(MATCH($E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2,$B$3:$B$7,0)),$E$2+ROW(OFFSET($B$2,0,0,$E$3-$E$2+1))-2),ROW(A1))

becomes

SMALL({2000000; FALSE; 2000002; FALSE; FALSE; 2000005; 2000006; FALSE; FALSE; 2000009; 2000010},ROW(A1))

becomes

SMALL({2000000; FALSE; 2000002; FALSE; FALSE; 2000005; 2000006; FALSE; FALSE; 2000009; 2000010},1)

and returns 2000000 in cell D6.

Get the Excel file

missing-values-in-a-column-1.xlsx

Missing numbers (vba)

The macro demonstrated here let´s you select a cell range (values must be in a single column), start and end number.

A new sheet is created, values are sorted in the first column. The second column (B) contains all missing values.

VBA

Sub Missingvalues()
Dim rng As Range
Dim rng1 As Range
Dim StartV As Double, EndV As Double, i As Double, j As Single
Dim k() As Double
Dim WS As Worksheet
ReDim k(0)
On Error Resume Next
Set rng = Application.InputBox(Prompt:="Select a range:", _
Title:="Extract missing values", _
Default:=Selection.Address, Type:=8)
StartV = InputBox("Start value:")
EndV = InputBox("End value:")
On Error GoTo 0
Set WS = Sheets.Add
WS.Range("A1:A" & rng.Rows.CountLarge).Value = rng.Value
With WS.Sort
    .SortFields.Add Key:=WS.Range("A1"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .SetRange Range("A1:A" & rng.Rows.CountLarge)
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
MsgBox "test"
Set rng1 = WS.Range("A1:A" & rng.Rows.CountLarge)
For i = StartV To EndV
    On Error Resume Next
    j = Application.Match(i, rng1)
    If Err = 0 Then
        If rng1(j, 1) <> i Then
            k(UBound(k)) = i
            ReDim Preserve k(UBound(k) + 1)
        End If
    Else
        k(UBound(k)) = i
        ReDim Preserve k(UBound(k) + 1)
    End If
    On Error GoTo 0
    Application.StatusBar = i
Next i
WS.Range("B1") = "Missing values"
WS.Range("B2:B" & UBound(k) + 1) = Application.Transpose(k)
End Sub

Get the Excel file

Find-missing-values-version2.xlsm