Lookup missing number(s) in column w/blanks & duplicates

[Excel-User-RR]

I am hoping someone can help construct some kind of Lookup+Find/Match formula
to check a column of numbers using a constant array provided e.g.
{1,2,3,4,5,6,7,8} and return the numbers in the column that are missing (if
any). The will be duplicate numbers and blanks, but I want to find out if any
of the numbers in the 1-8 array do not appear at all in the column. Numbers
may vary by column but will be no greater than 8, i.e. col.B: 1-5, col.C:
1-7. For the example below the results should be: Col.A is missing numbers 4
& 7; Col.B is missing number 3; Col.C is missing number 1. Unfortunately I
have no idea how to make this happen. I would greatly appreciate any
responses to help. Thank you!
A B C
2 1 3
2 4
8 7
2 4
3 1 2
1 5 5
6 7
5 6

 

[T. Valko]

Try this...

Create this defined name...

Goto the menu Insert>Name>Define
Name: Nums
Refers to: =ROW(INDIRECT("1:8"))
OK

Then, assume your numbers are in the range A2:A9.

Enter this array formula** in say, C2:

=SMALL(IF(ISNA(MATCH(Nums,A$2:A$9,0)),Nums),ROWS(C$2:C2))

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

 

[Excel-User-RR]

Thanks Biff, the suggestion works great. I made a slight modification to it
because some columns are 1-8, some are 1-6, some 1-5 etc. So I made a
separate defined name for each one that I needed, i.e. - Nums4, Nums5, Nums6,
Nums8. If you have a better suggestion to handle this difference, I would
definitely like to see it; otherwise I am happy will the answer as it is.
Thanks!

 

[T. Valko]

What you did is about the best way to do it.

Thanks for the feedback!