## Find missing numbers in a range containing multiple columns

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

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

Identify missing numbers in a column

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]

What values are missing in List 1 that exists i List 2?

Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]

Identify missing numbers in a range

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]

Identify missing numbers in two columns based on a numerical range

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]

Insert rows for missing values

HughMark asks: I have 2 columns named customer (A1) and OR No. (B1). Under customer are names enumerated below them. […]

Find missing dates in a set of date ranges

The formula in cell B8, shown above, extracts dates not included in the date ranges, in other words, dates that […]

How to calculate missing months in a given date range

Question: I have dates in a list. I would like to know how to identify missing months in this list […]

Identify missing three character alpha code numbers

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]

Highlight missing values between to columns

The picture above shows two lists. How do I highlight values in List 1 that are not in List 2? […]

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

### Leave a Reply

### How to comment

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

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

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

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

Put your VBA code here.

[/vb]

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

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

[...] 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. [...]