Thanks Biff, that works well. I have even combined them so both things are
checked at the same time. I have now finally worked out how to use
conditional formatting effectively and have set it up so it formats the cells
red if they are empty or duplicate for visual cues as well.

Thanks


"T. Valko" wrote:

vlookup("john smith",sheet2!A1:B10,2,False)

For the #N/A problem...

A1 = John Smith

=IF(COUNTIF(Sheet2!A$1:A$10,A1),VLOOKUP(A1,Sheet2! A$1:B$10,2,0),"")

That will return a blank.

For the duplicate name problem:

=IF(COUNTIF(Sheet2!A$1:A$10,A1)1,"Duplicate","Uni que")

--
Biff
Microsoft Excel MVP


"NDBC" wrote in message
...
I have a worksheet with a list of rider names and rider id tag no. EG.


A B
John Smith 256987
Rod Brown 236478

In another worksheet I use a vlookup of the riders name to return there id
tag number. ie. vlookup("john smith",sheet2!A1:B10,2,False)

This can fail for three reasons.

1. There are multiple people with the same name.
2. The name is spelt incorrectly
3. The name and tag id have not been entered in the original worksheet
(ie.
first time rider)

What I would like to do to help prevent reasons 2 and 3 from occuring is
make the cell that the vlookup formula is in go red if it can't find a tag
id
number (ie brings back #N/A).

Can this be done without vba.

I would also appreciate any ideas on checking for multiple riders with the
same name. Once each occurence occurs the names would be changed (ie
initials
added or something along those lines) but it would be good to have a check
to
make sure that when a first time rider comes along he does not have the
same
name as a rider currently on the list.

Thanks