If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Vlookup function...
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? |
#2
|
|||
|
|||
Vlookup function...
Withe the string to search for in B2:
=IF(COUNTIF('Employee Data'!A1:B1500,"*"&B2&"*")1,"Error",IF(ISNA(VLOOK UP("*"&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 "John Smith" wrote in message ... 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? |
#3
|
|||
|
|||
Vlookup function...
"Niek Otten" wrote in message ... Withe the string to search for in B2: =IF(COUNTIF('Employee Data'!A1:B1500,"*"&B2&"*")1,"Error",IF(ISNA(VLOOK UP("*"&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. |
#4
|
|||
|
|||
Vlookup function...
"John Smith" wrote in message ... "Niek Otten" wrote in message ... Withe the string to search for in B2: =IF(COUNTIF('Employee Data'!A1:B1500,"*"&B2&"*")1,"Error",IF(ISNA(VLOOK UP("*"&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. Also on testing, I have found that searching for Joe Bloggs when the username is joe.bloggs gives an "Employee not found" error. |
#5
|
|||
|
|||
Vlookup function...
The reason for the first part is to give an error when Fred Blogg exists too
-- Kind regards, Niek Otten Microsoft MVP - Excel "John Smith" wrote in message ... "Niek Otten" wrote in message ... Withe the string to search for in B2: =IF(COUNTIF('Employee Data'!A1:B1500,"*"&B2&"*")1,"Error",IF(ISNA(VLOOK UP("*"&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. |
Thread Tools | |
Display Modes | |
|
|