Smallest missing integer

  • Thread starter Thread starter Andrew B
  • Start date Start date
A

Andrew B

For the array a1:h3, I want to identify the smallest
integer not yet found in the array.

eg. if the array contains 1,2,3,4,6,7,9

I want the formula to return 5

thanx
Andrew
 
Hi
if you always start with the number 1 as smallest integer you may try
the following array formula (entered with CTRL+SHIFT+ENTER):
=MIN(IF(COUNTIF(A1:H3,ROW(INDIRECT("1:100")))=0,ROW(INDIRECT("1:100")))
)
 
For the array a1:h3, I want to identify the smallest
integer not yet found in the array.

eg. if the array contains 1,2,3,4,6,7,9

I want the formula to return 5
Click to expand...

So you mean smallest positive integer. Try the *array* formula

=MATCH(0,COUNTIF(A1:H3,ROW(INDIRECT("1:"&(MAX(A1:H3)+1)))),0)
 
Back
Top