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(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?





0
John
4/15/2010 10:34:45 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
719 Views

Similar Articles

[PageSpeed] 25

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?
> 
> 
> 
> 
> 
> .
> 
0
Utf
4/15/2010 11:40:01 AM
You could try this, to change any spaces to a dot and apply a second
lookup:

=3DIF(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=A0am, "John Smith" <jblo...@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 form=
at
> "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 employe=
e
> types a name in the format "Joe", "Bloggs", "joe.bloggs" or Joe Bloggs" i=
n
> the cell in column C, it will return the relevant payroll number in colum=
n
> D.
>
> If there is another employee called (say) Fred Bloggs and the employee ju=
st
> 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 give=
n
> and works quite well...
>
> =3DIF(COUNTIF('Employee
> Data'!A1:B1500,"*"&B2&"*")>1,"Error",IF(ISNA(VLOOKUP("*"&B2&"*",'Employee
> Data'!A1:B1500,2,FALSE)),"Employee not found",VLOOKUP("*"&B2&"*",'Employe=
e
> 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?

0
Pete_UK
4/15/2010 11:53:20 AM
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
0
Dave
4/15/2010 11:56:05 AM
"Pete_UK" <pashurst@auditel.net> wrote in message 
news:e0cce1ce-8cd6-4a25-858b-504e4614074e@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! 


0
John
4/15/2010 12:25:18 PM
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(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?
> 
> 
> 
> 
> 
> .
> 
0
Utf
4/15/2010 3:27:01 PM
Reply:

Similar Artilces:

Finding merged cells #2
Sorry for posting 2 messages with ref to the same problem but I am in dire need of a solution... I seem to have a problem due to cells in a worksheet being merged, how can i identify these cells? Many thanks, Rick Excel version? If you have 2002 or 2003 you can use Edit, Find, Format (and specify merged cells), Find All. -- Jim Rech Excel MVP "R D S" <ricknewsgroup@members.v21.co.uk> wrote in message news:385u2aF5kslgkU1@individual.net... | Sorry for posting 2 messages with ref to the same problem but I am in dire | need of a solution... | | I seem to have a problem d...

Workbook prints as 2 separate print jobs
When sending an Excel workbook consisting of 1-10 worksheets and choosing the Print Entire Workbook option, the print job prints as though it were two different print jobs sent to the printer. This happens when choosing a Xerox printer-copier, and also when choosing to print to Acrobat Distiller. Any fixes? Suggestions? Thanks. -- Alice. ...

How do I seperate 1 page into 2 in MS Publisher 2003?
I have created a "Newsletter" for my company in Publisher 2003. It is all 1 page and I would like to seperate it into 2 so that the print break doesnt happen in the middle of text boxes. Also how can I convert the "newsletter" into a printer friendly format. Some people are unable to print it correctly so I would like to send it out as a more printer friendly format. I did try converting it to a PDF but it was then 26mb which is too big to email obviously. Any help or suggestions would be greatly appreciated. Thank You WolfMaster <WolfMaster@discussions.microsoft....

2 different accounts
Me and my wife, we have both an email account, at the same server. My wifes computer is broken and I would like to make also for her a separated account in my windows mail on my computer. I have made a new account for her emailadress but now all her mails come in my mailbox. Is there a solution to split the accounts?? You can have up to 32 email accounts in Windows Mail. However, Windows Mail does not have identities like Outlook Express did. By default, all email from all accounts goes into the same Inbox.=20 There are four different ways of changing that: 1. Use separate Windo...

Customization Questions #2
We have some functuionality qs... The prospect will have a centralised Contacts DB managed in CRM. They are a law firm and hence more than 1 laywer will be associated with a Contact in the CRM contact base. They need a Customization which will allow them to select and add multiple lawyers (AD/CRM users) against a particular contact, So that when they open a contact form - there may be a button which when clicked can show them the AD users associated with the particular contact.Right now we only have the Owner in the admin tab... We hope this is possible by creating a look up to add CR...

Date functions
Our database represents dates by giving the day number within a year. For example, day number 1 is January 1, and day number 365 is December 31. Is there a function to convert day number to mmm-dd? =TEXT(DATE(2005,1,A1),"mmm-dd") where cell A1 holds the day of the year "Kirk P." <KirkP@discussions.microsoft.com> wrote in message news:847D34F3-CA15-4BA2-A5EB-578DBBB42E6A@microsoft.com... > Our database represents dates by giving the day number within a year. For > example, day number 1 is January 1, and day number 365 is December 31. Is > there a fun...

Mail merge & Publisher #2
When I try to choose a source for the mail merge and I choose a data base file that is a Microsoft Excel comma seperated value file or a Microsoft Excel worksheet. I always get a window that pops up that says... "This operation cannot be completed because of dialog or database engine failures. Please try Again later." Any help would be really appreciated. See if updating your MDAC components and Jet helps Microsoft Data Access Components (MDAC) 2.8 SP1 http://www.microsoft.com/downloads/details.aspx?FamilyID=78cac895-efc2-4f8e-a9e0-3a1afbd5922e&DisplayLang=en Jet 4.0 Servi...

Duplicate Mailbox in Outlook 2003 #2
I am running Outlook 2003 on a Windows XP SP2 machine. Outlook is configured to connect to an Exchange 2003 server. Under the "All Mail Folders" in Outlook 2003, it lists my mailbox twice - one with the Outlook Today icon and one with the Personal Folder icon (even though it is not a personal folder). Clicking on either one displays the exact same contents and folders. I tried going into the email account options to see if I can remove the duplicate account but there is only one Exchange account is listed. I am not running Outlook in cached mode. I do have a PPC I sync with...

Hotfix 1.2.0183
http://support.microsoft.com/?kbid=896347 Hi to all! Today my partner send me the new hotfix of Store Operation.. the 1.2.0183 .. but the hotfix isn't available into support.microsoft.com Anyone know what contain new hotfix? Thank you Antonio Addresses possible problems with Eltron Label printers. Pretty light hotfix! -- Glenn Adams Tiber Creek Consulting http://www.tibercreek.com glenn@tibercreek.com ---------------------------------------------- Please DO NOT respond to me directly but post all responses here in the newsgroup so that all can share the information "Anto...

Prevent functions from changing when new rows are added
I have a worksheet configured as a timesheet, where each row represents a task performed. For any given day, I track the task(s) performed and the hours spent on each task, and there are many functions in the worksheet. The worksheet contains the following columns: Column A: blank column (not used for anything). Column B: series of dates (with a blank row in between each date). Column C: tasks performed, manually entered. Column D: hours worked on the task. Column E: hours worked for the week. There's a function in each cell where, if it's Sunday, a sum of the week's...

Out of Office Question #2
I have Out of Office Assistant turned on and want to reply to all messages with a certain email. So I use a Rule and create a tempate with a check in the box 'Reply.' The problem is, when the mailbox recieves a message, it sends 2 emails back to the sender. One with the subject line that reads "Out of Office AutoReply: [email title] -." Also it sends the correct one based on my template. How do I prevent the first one from being sent? Office 2000 running via an Exchange server. Thanks for the help in Advanced! -Amon Amon Borland <AmonBorland@+nospam+gmail....

syncronise betwin 2 PC's ???
Hi I use two PC's a stationary and a laptop and Outlook 2000 and 2002(XP) and can't remember anymore what is where Is it possible to syncronise the two which at home is connected in a LAN so I have everything (concerning Outlook) on boath of them -- erik Denmark Take a look here, it may help: http://www.slipstick.com/outlook/sync.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.c...

task pad #2
What is the difference between these 2 Task Pad views in Outlook 2003: -All Tasks -Today's Tasks Thanks All Tasks also shows completed Tasks. "Dorothy" <Dorothy@discussions.microsoft.com> wrote in message news:D29D1515-954A-450D-9CD0-B832DC7DA088@microsoft.com... > What is the difference between these 2 Task Pad views in Outlook 2003: > > -All Tasks > -Today's Tasks > > Thanks ...

Combining IF and Count functions
I am a novice with excel and I need assistance creating a formula that will count the number of people that appear by date. How can I ask excel to complete the following calculation: If the date is "10/11/05" count each occurrence of "John"? Here is my data sample: Completion Date Contact Type Person 10-11-2005 Letter John 10-12-2005 Mail Paul 10-11-2005 Mail Richard 10-11-2005 Letter Paul 10-12-2005 Letter Paul 10-12-2005 Letter Paul 10-12-2005 Letter Paul 10-12-2005 Mail Richard 10-12-2005 Letter Paul 10-11-2005 Mail Richard 10-12-2005 Mail Jim 10-12-2005 Letter Pa...

Displaying 2 y Axis in different areas
I work in Excel 2000 and need to create a chart that displays data but i have to give the charts to my boss to read who is not very clever and gets very confused with them so i need a way of getting a y axis to display on both sides of the chart so that is lists what is on each side. at the moment it takes me ages using paint and other programs. Can it be done through excel charts -- Thank you for reading my post. Hopefully you can answer my querie Hi, You will find an explanation here http://www.andypope.info/tips/tip005.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http:...

CComboBox #2
Hi all. In CDialog, how do I get a CComboBox to remember what you typed into it when you leave the dialog box but keep the list active whenever you go back into the CDialog? Scott ...

&quot;New Web Query&quot; function
good day, are there any friends use New Web Query fuction under Data -> impor external data? My objective is to export (a list of stocks info) from web page t Excel spread sheet. e.g. the is is Microsoft's stock details (stock code: MSFT) http://finance.yahoo.com/q/ae?s=MSFT i can use "New Web Query" function to link this page to spread sheet. Question: how can I define a list of stocks (let say 10 differen stocks) that I want to monitor apart from creating 10 differen separate spreadsheets link with each individual stock code? any ideas are welcome thanks for your help ...

Can't upgrade HQ to 2.00
I try to upgrade HQ database from 1.3 reresh to 2.00 but I have Error "An error occurred while attempting ti upgrade the database 'DF_Configuration_SoftwareValidation1' is not a constraint " How should I do? Help me plz. >_<" -- TOY2TOY ...

#N/A #2
Would appreciate some help on this............ I have some formulas set up in a spreadsheet that other people will fill progressively. In cells where the formula has no feeder' data to provide a result it displays "#N/A". Is there something I can add to the formula to make the cell appear blank unless there is data to go in there? Hi Bunny! Use: =IF(ISNA(YourFormula),"",Yourformula) -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Thank you again Mr Harker "Norman Harker" <njharker@optusnet.com.au> wrote in me...

How do I calculate the week day hours between 2 days
I need to calculate the difference in hours between when I received a project and when when I completed the project. Within this time frame I do not want to count weekends and I want the workday to be 8:00 am to 8:00 pm. Example (9/11/5 8:00 am started and 9/12/5 8:00 am finished)= 12 hours not 24 hours Is there an easy way to do this? -- Mickey Take a look at Chip Pearson's solution at http://www.cpearson.com/excel/DateTimeWS.htm Steve "Mickey" <Mickey@discussions.microsoft.com> wrote in message news:9F5C1698-DED1-4285-846B-AD72CBBB01A3@microsoft.com... >I n...

Sorting by Number of Characters #2
Has anyone ever tried to sort records by number of characters? I need to go through a very large medication document and need to sort out the meds that are 30 characters or less. Anyone have any ideas on how to do this? Thanks! -- cny2 ------------------------------------------------------------------------ cny2's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25679 View this thread: http://www.excelforum.com/showthread.php?threadid=390956 Use a helper column. If the meds are in column A, in column B (or the next blank column) enter B1: =LEN(A1) and ...

CWnd class to ActiveX #2
Hi I have a windows control i.e. CWnd based. I would like to change it into ActiveX control so that I can use it on HTML page and VB. What might the easiest way to change it without rewriting the whole control? Any suggestion on any guide line/pit fall? thanks lordWHO wrote: > Hi > > I have a windows control i.e. CWnd based. > I would like to change it into ActiveX control so that I can use it on > HTML page and VB. > > What might the easiest way to change it without rewriting the whole > control? > Any suggestion on any guide line/pit fall? > > thanks ...

XML Serialization : Error reflecting field.... #2
I have to deserialize an XML document to objects and then serialize it back to XML to pass to the stored proc. I am attaching partial code. After this , I also have to serialize Here is the XML : <data> <orders> <order> <order_id>0</order_id> <issue_id>4460</issue_id> <action>add</action> </order> </orders> </data> This is the main.cs file where I call the Deserializer public class Main { public void AcceptOrdersDom( string strInputXML ) { OrderRootDeSerialize ordRoot =(OrderRootDeSerializ...

IF function #13
Hi all, Is it possible to have an IF function that deletes a row=20 if a condition is returned? I have a list of stock, with sub-totals throughout. I=20 want to delete all rows with sub-totals, so that i am only=20 left with the actual items. So, in the example below, i=20 would want to delete rows 3 and 5. A B=20 1 item1a =A35 2 item1b =A34 3 sub-total =A39 4 item2 =A32 5 item3a =A34 6 item3b =A36 7 sub-total =A310 the rows i want to delete, ll have sub-total written in=20 them. thanks for any help, Luc 6 Not possible with a fun...

Splitter windows #2
Is there any way to make a splitter pane always remain the same size regardless of resizing the main frame. My main frame has 3 static splitter panes. The Bottom pane is a form view, and the top pane is split with a tree view and a list view. I want the form view to always stay the same size when the user resizes the window. Thanks. Jo Franklin wrote: > Is there any way to make a splitter pane always remain the same size > regardless of resizing the main frame. My main frame has 3 static splitter > panes. The Bottom pane is a form view, and the top pane is split with a tree > v...