Whilst I hope that someone assists you with a formula it's not looking
promising so far.

The only way I can see this check could be performed is a somewhat
manual task of Copy-ing all the used names from Sheet1 to Sheet3 with a
Paste Special, Values, sort and create a second name-range Lookup
table.
Then on Sheet 2, for each line (use a cell to the right of the table)
do a lookup if a match is found from a Sheet2 item to the newly created
Sheet3 lookup table.

For the second part of your question, if you then copy the Sheet2 data
to Sheet4 and Paste Special, Values, and sort Sheet4 you will have a
list of what is used and what is missing.

It's not much, but will allow you to view what is/is-not a matching
entry, albeit as a manual check.

And as you say "The enclosed questions or comments are entirely mine
and do not represent the thoughts, views, or policy of my employer or
any other individual or group. Any errors or omissions are my own"
(I like that)


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=381757