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

Answer: C1 and C2 contains the range boundaries, A2:A6 (List) contains the list.

Formula in B5:B15:

=SMALL(IF(ISERROR(MATCH(ROW(INDIRECT(C1&":"&C2));List;0));ROW(INDIRECT(C1&":"&C2));"");ROW()-ROW(missing_list_start)+1) + CTRL + SHIFT + ENTER

Excel 2007 users can remove any #NUM errors with this formula:

=IFERROR(SMALL(IF(ISERROR(MATCH(ROW(INDIRECT(C1&":"&C2));List;0));ROW(INDIRECT(C1&":"&C2));"");ROW()-ROW(missing_list_start)+1);"") + CTRL + SHIFT + ENTER

Named ranges
List (A2:A6)
missing_list_start (B5)

What is named ranges?

How to customize the formula to your excel spreadsheet
Change the named ranges.

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

INDIRECT(ref_text,[a1])
Returns the reference specified by a text string

ROW(reference) returns the rownumber of a reference

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

Related posts:

1. Identify missing values in two columns using excel formula
2. Identify largest text value in a column using array formula in excel
3. Identify missing three character alpha code numbers in excel
4. Identify missing numbers in a range in excel
5. Extract a list of duplicates from a column using array formula in excel
6. Identify duplicate invoice records in excel
7. How to automatically fill all blanks with missing data or formula
8. How to calculate missing months in a given date range in excel
9. Unique list to be created from a column where an adjacent column has text cell values
10. Filter a column and create a new unique list sorted from A to Z using array formula in excel