Author: Oscar Cronquist Article last updated on February 24, 2018

Table of contents

  1. Missing numbers (array formula)
  2. Missing numbers (vba)

Missing numbers (array formula)

Question: I want to find missing numbers in a range?

Answer: C1 and C2 contain the range boundaries.

Array Formula in B5

=SMALL(IF(ISERROR(MATCH($C$1+ROW(OFFSET($A$1, 0, 0, $C$2-$C$1+1))-1, $A$2:$A$6, 0)), $C$1+ROW(OFFSET($A$1, 0, 0, $C$2-$C$1+1))-1), ROW(A1))

How to create an array formula

  1. Select cell B5
  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 B5
  2. Select cell B6:B11
  3. Paste

Download excel sample file for this tutorial.
(Excel 2007 Workbook *.xlsx)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

returns the rownumber of a reference

Returns the k-th smallest row number in this data set.

Returns a reference to a range that is a given number of rows and columns from a given reference.

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.


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
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
        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

Download *.xlsm file