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.
missing-values-in-a-column.xlsx
(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

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

ROW(reference) returns the rownumber of a reference

SMALL(array,k) Returns the k-th smallest row number in this data set.