Author: Oscar Cronquist Article last 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?

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.

find missing numbers in a range from multiple columns1.xlsx

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

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.