A little while ago I received an email from Deborah asking what formula she can use to list missing numbers from a range.

I’ve seen loads of different approaches to this over the years but today I’m going to share with you the formula I think is the most elegant.

Here is Deborah’s list of numbers:

You’ll notice they’re not sorted and there is a blank cell in the middle of the range. So, our formula needs to be able to handle blanks and an unsorted list.

And here she is:

=SMALL(IF(COUNTIF($A$1:$A$7,ROW($1:$12))=0,ROW($1:$12),""),ROW(A1))

It’s an array formula so you need to enter it with CTRL+SHIFT+ENTER.

Below you can see the results of the formula copied down column C. When there are no more missing numbers it returns a #NUM! error.

**Tip: **You can easily avoid the #NUM! errors by wrapping it in an IFERROR function if you prefer.

### In English the Formula in Cell C1 Reads:

Count the numbers in the range A1:A7, that match the numbers generated by ROW($1:$12) i.e. {1;2;3;4;5;6;7;8;9;10;11;12}, if they = 0 (i.e. if they’re missing), give me a list of them, if they’re not = 0 (i.e. not missing), give me "" (i.e. nothing), and in this cell just return the 1^{st} smallest missing value.

The cell reference in the ROW(A1) part of the formula is relative, so as you copy the formula down column C, ROW(A1) becomes ROW(A2) which =2 and returns the second smallest missing number, ROW(A3) which is 3, returns the third smallest missing number and so on.

## Functions Used in this Formula

### SMALL

The SMALL function returns the smallest k^{th} value in an array. e.g the first smallest, second smallest, third smallest and so on.

The syntax is SMALL(array,k)

### ROW

This ROW formula: ROW(A1) returns the row number of a reference. In this case it would return 1.

This ROW formula: ROW($1:$12) returns an array of values from 1 to 12 like this {1;2;3;4;5;6;7;8;9;10;11;12}. It works this way because this is an array formula entered with CTRL+SHIFT+ENTER.

### COUNTIF

The COUNTIF function is counting the numbers in the range A1:A7 that match the array of values returned by the ROW function {1;2;3;4;5;6;7;8;9;10;11;12}.

The syntax is: COUNTIF(range, criteria)

### IF

The IF function tests for counts of 0, and if TRUE i.e. zero (remember if the count is zero it means the number is missing), it then returns the corresponding value from the resultant array generated by ROW($1:$12) i.e. {1;2;3;4;5;6;7;8;9;10;11;12}, otherwise it returns nothing.

The syntax is: IF(logical_test, value_if_true, value_if_false)

## Let’s Step Through How It Evaluates:

The key here is that array formulas evaluate on an array of items i.e. more than one item, within the one formula. Below you will see evidence of this as the formula evaluates.

=SMALL(IF(COUNTIF($A$1:$A$7,ROW($1:$12))=0,ROW($1:$12),""),ROW(A1))

**Step 1** - The values in the range A1:A7 and ROW(1:12) are returned:

=SMALL(IF(COUNTIF({1;7;5;8;;10;12},{1;2;3;4;5;6;7;8;9;10;11;12})=0,ROW($1:$12),""),ROW(A1))

**Step 2** - The COUNTIF then returns (a resultant array of) the counts of values in the array returned by the ROW(1:12) formula that are present in the range A1:A7:

=SMALL(IF({1;0;0;0;1;0;1;1;0;1;0;1})=0, ROW($1:$12),""),ROW(A1))

i.e. in the range A1:A7 there is 1 number 1 in the range, there are 0 number 2’s, 0 number 3’s etc.

**Step 3** – The IF function’s logical test evaluates returning a TRUE where the number in the resultant array =0, and a FALSE where it doesn’t:

=SMALL(IF({FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE}, {1;2;3;4;5;6;7;8;9;10;11;12},""),ROW(A1))

**Step 4** – the IF function’s value_if_true and value_if_false evaluate:

=SMALL({"";2;3;4; "";6;"";"";9; "";11;""},ROW(A1))

We now have a resultant array of the missing numbers.

**Step 5** – The final ROW function evaluates to 1:

=SMALL({"";2;3;4; "";6;"";"";9; "";11;""},1)

**Step 6** – the SMALL function finds the 1^{st} smallest value in the array:

`=2`

**Tip**: the ROW function is used as a short cut to avoid having to type in an array constant. i.e. it’s quicker to type ROW($1:$12) than it is to type {1;2;3;4;5;6;7;8;9;10;11;12}

**Note**: This formula will ignore duplicate values in the list since it’s only looking for numbers in the range that have a count of 0, i.e. are missing.

## Limitations of This Formula:

- It uses the ROW function to generate a list of values that should be in Deborah’s range. It is therefore limited to the number of rows in your workbook. If you’re using Excel 2003 you’re limited to numbers in the range 1 to 65,536, and if you’re using Excel 2007 onwards you’re limited to number between 1 and 1,048,576.
- It only works with positive numbers >0.
- It only works with whole numbers/integers.

Here is a clever VBA solution to find missing numbers that gets around some of the limitations above, plus the added bonus of allowing you to choose whether you want your list of missing values returned in a vertical or horizontal list.

## Formula Challenge

What if your sequence of numbers were all negative. What formula would you use to find the missing negative numbers?

Please leave your answer in the comment field below.

## Thanks

I’d like to thank Deborah for asking this question. It reminded me that I should write about this formula.

I’d also like to thank Oscar Cronquist of Get Digital Help for sharing his formula.

If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+1, Facebook and Twitter.

Trish L. says

I am working with invoice numbers entered manually into a large worksheet that is vital to tracking cost and sales margins. Each invoice number may have multiple line items if there is more than 1 item on the invoice. Sometimes during the course of the year I fail to input an invoice number because of the multiple places I have to put that information and the sheer volume of invoices on some days and don’t realize it until the invoice is paid. The list is in a table format so the range is named. The list grows daily. Each year I would have a new starting invoice number.

My invoice numbers are in column O entitled Inv#. I need it to either highlight the two numbers around which the invoice number is missing or list it on a separate sheet in the workbook.

Catalin Bombea says

Hi Trish,

Can you upload a sample file on our forum so we can see your data structure? (create a new topic after sign-up)

Thank you

Catalin

Dr. Nazir Ahamd Bhat says

I am a working Librarian and was presently in the processing of carrying out stock verification of books, around 40 thousand numerical accession numbers. I was in search of this type of formula. Thanks a lot for your people for sharing your wit and expertise. I am really thankful and hope you will be sharing your knowledge with same spirit in future, as well, simply for the betterment of the humanity.

Philip Treacy says

You’re welcome, glad you found this useful.

Phil

Bryan Schilling says

I am currently trying to find away to find missing numbers in a sequence, but want the formula to reference a number in a cell to change the range. For example, In cell A1 I have the formula =sum($b$1:$b$8)

I want to replace ONLY THE 8 above with a cell reference…for example in cell D1 I have the number 9. So I’m trying to use that same formula logic =SUM(INDIRECT(“B1:B”&D1)) in the missing sequence formula so that I don’t have to update the formula each time I paste in new data.

In the Formula below the number for the start of the range is in H2 the end of the range in J2 and the range of numbers is in column I and the start of the missing list start is J4.

It with either tell me too many arguments or formula is broken. Please help if you can fix.

=SMALL(IF(COUNTIF(I$4:I$1048576,ROW(INDIRECT(“I”&H2):INDIRECT(“I”&J2))=0,ROW(INDIRECT(“I”&H2):INDIRECT(“I”&J2),””),ROW(J4))

Catalin Bombea says

Hi Bryan,

No idea what your formula does, looks really messy.

Just to correct the functional errors:

Zana says

Great job!

Thank you, Mynda!

Ruthie A. Ward says

For myself, I’d use a helper column to convert the negatives to positive numbers, run the array formula, and then convert the output back to a negative number.

Using the same idea of a helper column, non-integers can be assessed. Multiply the original value by powers of 10 until you get an integer and process it. Convert the output back by dividing by the same power of 10 as used previously. A few extra steps are needed to finish the process. First, use a round function to get the right number of decimal places. Copy and paste the values into the list you’re trying to generate. Lastly, highlight the values you just pasted and remove the duplicates. A bit complicated, but it works.

Mynda Treacy says

Cheers, Ruthie. I like your solution for handling non-integers too 🙂

r says

hi Mynda!

the second Limitation can be solve:

then we can write a general solution so:

where rng is $A$1:$A$7

regards

r

Mynda Treacy says

Ciao r,

Your ‘general solution’ is ideal in that it handles negative right through to positive, including zero. And perhaps best of all it detects the size of the number range. Brilliant!

Thanks for sharing.

Mynda.