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

1 3 4
5 6 7
8 8 3

Answer:

Formula in B8:

=SMALL(IF(COUNTIF(tbl, ROW($1:$9))=0, ROW($1:$9), ""), ROWS(B8:$B$8)) + CTRL + SHIFT + ENTER copied down as far as needed.

Named ranges
tbl (B3:D5)
What is named ranges?

How to implement array formula to your workbook
Change the named range. If your list starts at, for example, F3. Change B8:$B$8 in the above formulas to F2:$F$2. If you need to look for missing values 1-49, change $1:$9 in the above formula to $1:$49.

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

COUNTIF(range,criteria)
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

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