Vlookup function...

[John Smith]

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?

 

[Niek Otten]

Withe the string to search for in B2:

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

Watch the line breaks!

 

[John Smith]

Withe the string to search for in B2:

=IF(COUNTIF('Employee
Data'!A1:B1500,"*"&B2&"*")>1,"Error",IF(ISNA(VLOOKUP("*"&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

Thanks Niek, what is the reason for this first part as it seems to work
either with or without this function.

=IF(COUNTIF('Employee Data'!A1:B1500,"*"&B2&"*")>1,"Error"

I have found that $A$1:$B$1500 works when replicating the formula down the
column.

 

[John Smith]

Thanks Niek, what is the reason for this first part as it seems to work
either with or without this function.

=IF(COUNTIF('Employee Data'!A1:B1500,"*"&B2&"*")>1,"Error"

I have found that $A$1:$B$1500 works when replicating the formula down the
column.

Also on testing, I have found that searching for Joe Bloggs when the
username is joe.bloggs gives an "Employee not found" error.

 

[Niek Otten]

The reason for the first part is to give an error when Fred Blogg exists too