VLOOKUP function part 2...
in [General]
|
From: John Smith on 15 Apr 2010 06:34 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?
From: Ms-Exl-Learner on 15 Apr 2010 07:40 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","")))) -- Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "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(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? > > > > > > . >
From: Pete_UK on 15 Apr 2010 07:53 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 On Apr 15, 11:34 am, "John Smith" <jblo...(a)nospam.net> 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(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?
From: Dave Peterson on 15 Apr 2010 07:56 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 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(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? -- Dave Peterson
From: John Smith on 15 Apr 2010 08:25 "Pete_UK" <pashurst(a)auditel.net> wrote in message news:e0cce1ce-8cd6-4a25-858b-504e4614074e(a)z3g2000yqz.googlegroups.com... 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!
|
Next
|
Last
Pages: 1 2 Prev: Unpivoting a worksheet Next: Print and shrink automatically so that worksheet fits on paper? |