Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP function part 2...
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP function part 2...
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,"*"&She et2!$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,COU NTIF(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(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? . |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP function part 2...
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("*"&SUBSTITUT E(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" 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP function part 2...
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(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? -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
VLOOKUP function part 2...
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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with part match | Excel Worksheet Functions | |||
Part of VLOOKUP not working | Excel Worksheet Functions | |||
vlookup part of string... | Excel Discussion (Misc queries) | |||
How do I use VLOOKUP to find part of string? | Excel Worksheet Functions | |||
Vlookup for part of a word | Excel Worksheet Functions |