## Find missing numbers in a range from multiple columns

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

### Category: Missing values

Identify missing values in a column using excel formula

Table of contents Missing numbers (array formula) Missing numbers (vba) Missing numbers (array formula) Question: I want to find missing […]Comments(25) Filed in category: Excel, Missing values

Identify missing numbers in a range in excel

Question: How do I find missing numbers between 1-9 in a range? 1 3 4 5 6 7 8 8 […]Comments(5) Filed in category: Excel, Missing values

Identify missing values in two columns using excel formula

Question: I want to find missing numbers in two ranges combined? They are not adjacent. Answer: Array formula in cell […]Comments(4) Filed in category: Excel, Missing values

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 […]Comments(4) Filed in category: Compare, Excel, Missing values

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. […]Comments(3) Filed in category: Excel, Missing values

Find missing dates in a set of date ranges

John S asks: I would like to find the dates MM/DD/YYYY missing in a set of date ranges. I haven't […]Comments(3) Filed in category: Dates, Missing values

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 […]Comments(2) Filed in category: Dates, Excel, Missing values

Identify missing three character alpha code numbers in excel

This blog article answers a comment in this blog article: Identify missing values in two columns using excel formula Question: […]Comments(1) Filed in category: Excel, Missing values

Lookup between two lists of data to highlight missing data using conditional formatting in excel

To the right is a picture of two lists. How do I highlight values in List 1 that are not […]Comments(0) Filed in category: Conditional formatting, Excel, Missing values

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

### Leave a Reply

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

<code>your formula</code>

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