Looking for missing values .. array night mares

Hi all,

I have a list of 100,000 numeric identifiers.. 1,2,,4,5, ....100,000 etc.. and I am looking to return the missing numbers. E.g. 3
however any array formula takes forever and breaks the system

Using this formula, but as I say, when I expand this for the long list to read.. it crashes...



=SMALL(IF(ISERROR(MATCH($C$1+ROW(OFFSET($A$1, 0, 0, $C$2-$C$1+1))-1, $A$2:$A$6, 0)), $C$1+ROW(OFFSET($A$1, 0, 0, $C$2-$C$1+1))-1), ROW(A1))

Any, non array options or ways not to crash and continuously re calcualte is appreciated

Cheers

John

Try this array formula

Formula:

Please Login or Register  to view this content.

Confirmed with CTRL+SHIFT+ENTER
Copy down