EDIT: See this blog post on a shorter easier formula: Identify missing numbers in a range in excel.

Question: I have numbers spanning over multiple columns. I want to know the smallest value and the largest value and the values in between that are missing?

Answer:
Max value (F1) : =MAX(A1:C3)

Min value (F2): =MIN(A1:C3)

Creating a list of series of numbers between max and min values (H2:H12):
=IF((F1-F2+1)>=ROW()-1,ROW(INDIRECT(F2&":"&F1)),"") + Ctrl + Shift + Enter

Creating cell coordinates of not empty cells (I2:I12):
=IF(SUM(IF(A1:C3<>"",1,0))>=ROW()-1,SMALL(IF(A1:C3<>"",VALUE(ROW(A1:C3)&","&COLUMN(A1:C3))),ROW()-1),"") + Ctrl + Shift + Enter

Creating row coordinates from the cell range A1:C3 (J2:J12):
=IF(SUM(IF(A1:C3<>"",1,0))>=ROW()-1,SMALL(IF(A1:C3<>"",ROW(A1:C3)),ROW()-1),"") + Ctrl + Shift + Enter

Creating column values (K2:K12):
=(I2-J2)*10 copied down to K12

Getting column values (L2:L12):
=INDIRECT(ADDRESS(J2,K2)) copied down to L12

Calculate missing values (M2:M12):
=IF(ISERROR(MAX(IF(OR(H2=$L$2:$L$14),"",ROW($L$2:$L$14)-1))),"", MAX(IF(OR(H2=$L$2:$L$14),"",H2))) + Ctrl + Shift + Enter copied down to M12

Download excel sample file for this article.
find-missing-numbers-in-a-range-from-multiple-columns
(Excel 97-2003 Workbook *.xls)

Functions in this article:

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 row number of a reference

COLUMN(reference)
returns the column number of a reference

ADDRESS(row_num,column_num)
creates a cell reference as text, given specified row and column numbers

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

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

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

MAX(number1,[number2],)
Returns the largest value in a set of values. Ignores logical values and text.

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text.

EDIT: See this blog post on a shorter easier formula: Identify missing numbers in a range in excel.

Related posts:

1. Identify missing numbers in a range in excel
2. Identify missing three character alpha code numbers in excel
3. Identify missing values in two columns using excel formula
4. Identify missing values in a column using excel formula
5. How to calculate missing months in a given date range in excel
6. Count unique distinct numbers across multiple sheets (3D range) in excel
7. Extract numbers and text from a range using array formula in excel
8. Categorize values into multiple columns (excel formulas)
9. Create a list of all numbers or text in a column in excel
10. Lookup between two lists of data to highlight missing data using conditional formatting in excel