Find next available smallest number in a range

Hi there,

I'm a bit stumped with this apparently simple question. My array formula skills seem to have abandoned me this morning.

Suppose you have a list of numbers in a range like this:

Please Login or Register  to view this content.

I want to find the smallest number that has not been used. In this case it would be 5.

Thank you

Pls attach sample file

I assume you want a positive integer. 0 is less than 5 and it hasn't been used.

Originally Posted by Khalidngo

Hi Peter,

Use this {array formula} :
=MIN(IF(ISNA(MATCH(ROW($1:$400),A$2:A$401,0)),IF(COUNTIF(C$1:C1,ROW($1:$400))=0,ROW($1:$400))))

As seen here:
http://answers.microsoft.com/en-us/o...6988cde?auth=1

Regards,

What a beautiful formula - thank you so much.

(Crooza - yes, in my example I meant a positive integer but I've since adapted Khalidngo's formula to work with any given lower bound)

or try this one. assuming you want the smallest (starting from the smallest already there) as an array formula

=MIN(IF(ISERROR(MATCH(ROW(A1:A6)-1+MIN(A1:A6),A1:A6,0)),ROW(A1:A6)-1+MIN(A1:A6),99999999))

Here's another one...

Assuming the range of numbers is from 1 to 100.

Data Range

	A	B	C
1	1		5
2	7
3	2
4	2
5	4
6	3
7	------	------	------

This array formula** entered in C1:

=MIN(IF(ISNA(MATCH(ROW(INDIRECT("1:100")),A1:A6,0)),ROW(INDIRECT("1:100"))))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Originally Posted by Tony Valko

=MIN(IF(ISNA(MATCH(ROW(INDIRECT("1:100")),A1:A6,0)),ROW(INDIRECT("1:100"))))

Hi Biff,
Nice formula,

Just a question, INDIRECT necessary here?

Regards,

=MIN(IF(ISNA(MATCH(ROW(INDIRECT("1:100")),A1:A6,0)),ROW(INDIRECT("1:100"))))

Originally Posted by Khalidngo

Just a question, INDIRECT necessary here?

INDIRECT makes the formula more robust.

If we use:

ROW(1:100)

And happen to insert new rows before row 1 then the formula will return incorrect results.

ROW(1:100)

Becomes ROW(2:101)

So that means we start searching for the missing numbers 2 through 101 instead of 1 through 100.

INDIRECT can account for that.

ROW(INDIRECT("1:100"))

Will always refer to row 1 through 100.

Another non-array formula:

=MATCH(0,INDEX(COUNTIF($A$1:$A$6,ROW(INDIRECT("1:100"))),),0)

You're welcome!