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

Question: How do I find missing numbers between 1-9 in a range?

1 3 4
5 6 7
8 8 3


Identify missing numbers in a range

Array formula in cell B8:

=SMALL(IF(COUNTIF(tbl, ROW($1:$9))=0, ROW($1:$9), ""), ROW(A1))

If you have numbers between 5 and 32, change ROW($1:$9) to ROW($5:$32)

The formula won´t work if you have numbers above 1048576 (excel 2007). See this post:
Identify missing values in a column using excel formula

How to create an array formula

  1. Copy above array formula
  2. Select cell B8
  3. Click in formula bar
  4. Paste array formula in formula bar
  5. Press and hold Ctrl + Shift
  6. Press Enter

Named ranges
What is named ranges?

Download excel example file.
Missing numbers in a range.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

ROW(reference) Returns the rownumber of a reference

Counts the number of cells within a range that meet the given condition

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

ROWS(array) returns the number of rows in a reference or an array

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