Smallest missing integer

[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

 

[Frank Kabel]

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")))
)

 

[Harlan Grove]

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

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

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