VLookup? Right function?

[Timmers]

HI there!

I have two lists of which there is data removed from one of them. I am
trying to bring back the data that has been removed please?

For example: Column 1 has the numbers 1 2 3 4 5 6 7 8 9 and Column 2 has the
numbers 6,3,2,8. How do I produce the numbers 1,4,5,7,9 in another column
please?

Many thanks! :-)
James T
Bath, UK

[Biff]

Hi!

Here's one way:

Assume numbers 1-9 are in column A, A1:A9.
Assume numbers 6,3,2,8 are in column B, B1:B4

Enter this array formula with the key combo of
CTRL,SHIFT,ENTER in C1 and copy down until you get #NUM!
errors meaning all the pertinent data has been returned:

=INDEX(A$1:A$9,SMALL(IF(COUNTIF(B$1:B$4,A$1:A$9)=0,ROW
(A$1:A$9)),ROW(1:1)))

Biff

>-----Original Message-----
>HI there!
>
>I have two lists of which there is data removed from one
of them. I am
>trying to bring back the data that has been removed
please?
>
>For example: Column 1 has the numbers 1 2 3 4 5 6 7 8 9
and Column 2 has the
>numbers 6,3,2,8. How do I produce the numbers 1,4,5,7,9
in another column
>please?
>
>Many thanks! :-)
>James T
>Bath, UK
>.
>