Article updated on February 24, 2018

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)

Array formula in cell B8:

=SMALL(IF(COUNTIF($B$3:$D$5, ROW(INDEX($A$1:$A$10000, $E$10):INDEX($A$1:$A$10000, $E$9))), "", ROW(INDEX($A$1:$A$10000, $E$10):INDEX($A$1:$A$10000, $E$9))), ROWS($A$1:A1))

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Download Excel *.xlsx file

find missing numbers in a range from multiple columns1.xlsx

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.

Read this:  Identify missing numbers in a range in excel.