Here's another one...

Array entered** in F1:

=SMALL(IF(ISNA(MATCH(ROW(A$1:A$500),D$1:D$2000,0)) ,ROW(A$1:A$500)),ROWS(F$1:F1))

** 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.

Copy down until you get #NUM! errors meaning all missing numbers have been
returned. This is kind of slow to calculate.

Here's a macro from JMB that will do this. Place the code in a general
module.

Sub FindMissing()
Dim lngUpper As Long
Dim lngLower As Long
Dim i As Long
Dim rngData As Range
Dim lngcount As Long

Set rngData = Range("D1:D2000") 'change as needed
lngLower = 1 'start of sequence
lngUpper = 500 'end of sequence
lngcount = 1

For i = lngLower To lngUpper
If Not IsNumeric(Application.Match(i, _
rngData, 0)) Then
Range("F" & lngcount).Value = i 'sets output to column F
lngcount = lngcount + 1
End If
Next i

End Sub

--
Biff
Microsoft Excel MVP


"Vic" wrote in message
...
How do I find missing numbers?
I have numbers from 1 to 500 in columns D1 to D2000 (some numbers may
appear
in several cells). However, some numbers don't appear at all. How do I
find
missing numbers?
Thnak you.