VLOOKUP function part 2...

[John Smith]

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(VLOOKUP("*"&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?

 

[Ms-Exl-Learner]

Assume that the Employee data is in Sheet1 from A to B Column and you would
like to input characters in A1 of Sheet2

Copy and paste the below formula in Sheet2 B1 cell
=IF(TRIM(A1)="","",IF(COUNTIF(Sheet1!$A:$A,"*"&Sheet2!$A1&"*")=0,"Employee
not
found",IF(COUNTIF(Sheet1!$A:$A,"*"&Sheet2!$A1&"*")=1,VLOOKUP("*"&TRIM($A1)&"*",Sheet1!$A:$B,2,FALSE),IF(COUNTIF(Sheet1!$A:$A,"*"&Sheet2!$A1&"*")>1,COUNTIF(Sheet1!$A:$A,"*"&Sheet2!$A1&"*")&" "&TRIM($A1)&" MATCHING",""))))

 

[Pete_UK]

You could try this, to change any spaces to a dot and apply a second
lookup:

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

Hope this helps.

Pete

 

[Dave Peterson]

This isn't a general solution, but you could use =substitute() to remove the
dots from your string.

=substitute(a1,"."," ")
will replace those dots with spaces. You can incorporate that in your formula.

But if you have data that looks like Joe-Bloggs or Joe,Bloggs or Joe|Bloggs or
any other funny character, you'll have more work to do.

When I have to do this kind of thing, I'll do my best to make the formula as
easy as I can -- but I'll spend lots of time cleaning up the data.

I know that this is a miserable job -- and if you don't control the data, you
may have to do it over and over and over and ....

 

[John Smith]

You could try this, to change any spaces to a dot and apply a second
lookup:

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

Hope this helps.

Pete

***************************************************************

Very much appreciated all of you, thanks!

 

[Reg]

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