I see a lot of people have aswered the specific question but I wondered where
this would end?

for example: Joe.Blogs or j.bloggs or joeblogs. or j.blogg (ad nauseam)

from the basic formula you are asking for a generalised data cleansing
solution and that is a world of pain - surely the inputter should take SOME
repsonsibility?


Reg Migrant

"John Smith" wrote:

Apologies for the repeat thread, just there is so much traffic here that the
last part of my query went without an answer.

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 which was very kindly given
and works quite well...

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

But n testing this, I have found that searching for 'Joe Bloggs' when the
username is 'joe.bloggs' (with a separating dot) gives an "Employee not
found" error. It works with all the other criteria I specified, just not
this one.

Is there anything I can add to it that will make it meet these criteria?





.