## Find missing numbers in a range containing multiple columns

*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:**

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.

### One Response to “Find missing numbers in a range containing multiple columns”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[...] Find missing numbers in a range from multiple columns | Get Digital Help Ai si un XLS de exemplu acolo pe care il poti downloada. GL. [...]