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