Withe the string to search for in B2:

=IF(COUNTIF('Employee
Data'!A1:B1500,"*"&B2&"*")1,"Error",IF(ISNA(VLOOK UP("*"&B2&"*",'Employee
Data'!A1:B1500,2,FALSE)),"Emplyee not found",VLOOKUP("*"&B2&"*",'Employee
Data'!A1:B1500,2,FALSE)))

Watch the line breaks!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"John Smith" wrote in message
...
Sheet "Employee Data" holds usernames of about 1500 employees in the
format "joe.bloggs" in column A and employee payroll numbers in the format
00001, 00002, etc in column B

I am looking for a formula so that in another worksheet, where an employee
types a name in the format "Joe", "Bloggs", "joe.bloggs" or Joe Bloggs" in
the cell in column C, it will return the relevant payroll number in column
D.

If there is another employee called (say) Fred Bloggs and the employee
just types in "Bloggs", I would like it to return some sort of error
because there are two Bloggses.

Data starts in row 3

Currently I was looking at the following in D3...

=IF(ISNA(VLOOKUP(C3, 'Employee Data'!, 2, FALSE)) = TRUE, "Employee not
found", C3, 'Employee Data'!, 2, FALSE.

Obviously needs some more work what with the different entries that an
employee might enter, but I believe VLOOKUP has the potential to find part
entries - maybe needs * as a wildcard character?

Currently the formula does not work as I'm expecting - is this because I
need a range to search (A1:B200) rather than an entire sheet? Even with
the range it does not work - any ideas?