Partial Matching HELP NEEDED...SOS!!

I am struggling with this and wondering if you could help..

Worksheet 1 Column A & B
Keyword	Category
telecom	Telecom
tele com	#N/A

Worksheet 2 Column A & B
term	category
telecom	Telecom
telcom	Telecom
tele com	Telecom

What I want to do is pull the category name from worksheet 2 on to
worksheet 1.

=VLOOKUP("*"&A2&"*",Sheet1!B:B,1,0)

Is not working...any ideas?
0
2/15/2011 9:04:15 PM
excel 39879 articles. 2 followers. Follow

1 Replies
455 Views

Similar Articles

[PageSpeed] 39

"MikeMikeMike" <michael.lovelady@gmail.com> wrote in message 
news:8379f02e-a8e2-48ed-b8ee-1b768ffbddc3@k15g2000prk.googlegroups.com...
>I am struggling with this and wondering if you could help..
>
> Worksheet 1 Column A & B
> Keyword Category
> telecom Telecom
> tele com #N/A
>
> Worksheet 2 Column A & B
> term category
> telecom Telecom
> telcom Telecom
> tele com Telecom
>
> What I want to do is pull the category name from worksheet 2 on to
> worksheet 1.
>
> =VLOOKUP("*"&A2&"*",Sheet1!B:B,1,0)
>
> Is not working...any ideas?


Try:

=VLOOKUP("*"&A2&"*",Sheet1!A:B,2,0)

(Unless you mean Sheet2 ... your explanation and formula didn't seem to 
match.)
-- 
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)


0
2/15/2011 9:32:45 PM
Reply:

Similar Artilces:

Help on adjusting # of rows
I am creating a workbook for my estimating company. To link jobs together throughout the different spreadsheets, i have clicked on the column, copied it , and paste specialed a link to the other spreads. The only problem with this is that it has made the size of my document 28 MB... My question is, is there any way that you can manually adjust the amount of rows in a spreadsheet, or even more helpful, is there a way that you can only print cells that have actual text inside of the cell. If this is not possible, I have to manually go in and highlight what i want to print, and deal with the 28 m...

How to match and sort
Hi, I was wondering if there was a function or simple program to matc and sort two colums. Let's say the first column is a list compiled b person A and the second is a list compiled by person B. So what I nee done is to match the ones they have in common and filter out the one that are different. For example, let say we're comparing names and birthdays: Birthday (A)Name (A)Name (B) Birthday (B) 6/14 Josh Josephine 3/16 2/15 Chris Sharon 9/4 9/4 Sharon Jerry 8/11 2/28 Lillian Susan 7/12 8/11 Jerry I want it so that when I compare the two lists, it would become: Birthday (A)Name...

Partial Merge
What is the Partial Merge feature in Windows Server 2008 r2? I believe this is what you are looking for: http://blogs.msdn.com/dstfs/archive/2009/05/04/partial-merges-in-tfs-a-guide.aspx -- Paul Bergson MVP - Directory Services MCTS, MCT, MCSE, MCSA, Security+, BS CSci 2008, 2003, 2000 (Early Achiever), NT4 Microsoft's Thrive IT Pro of the Month - June 2009 http://www.pbbergs.com Please no e-mails, any questions should be posted in the NewsGroup This posting is provided "AS IS" with no warranties, and confers no rights. "Charle" <Charle@l...

Loop help
Hello all, I created (recorded) a macro to sort and subtotal raw data with the following: Sub SubtotalBalSheet() ' ' SubtotalBalSheet Macro ' Macro recorded 10/15/2004 by A24479 ' ' Rows("1:1").Select Selection.Insert Shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "G/L" Range("B1").Select ActiveCell.FormulaR1C1 = "Branch" Range("C1").Select ActiveCell.FormulaR1C1 = "Description" Range("D1").Select ActiveCell.FormulaR1C1 = "Current Month&...

Help: MS Outlook XP
Hi guys, I am having problems in copying the outlook's shortcuts from one machine from another. Can anyone help? Thanks & Regards, Keng Soon What exact shortcuts and where do you get stuck? -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "KS" <eksoon@streamyx.com> wrote in message news:eK18%23tZhEHA.644@tk2msftngp13.phx.gbl... > Hi guys, > > I am having problems in copying the outlook's shortcuts from one machine > from ...

Help with a Query 02-29-08
How would I do this in Access: I keep getting errors: update target set Assemrec = source.Recno from @Components target Join @Components source on '<$-' + target.RefDes + '>' = source.RefDes On Fri, 29 Feb 2008 12:54:11 -0800 (PST), "the_grove_man@yahoo.com" <the_grove_man@yahoo.com> wrote: >How would I do this in Access: I keep getting errors: > >update target >set Assemrec = source.Recno >from @Components target Join @Components source on '<$-' + >target.RefDes + '>' = source.RefDes This isn...

Need help converting Microsoft Outlook calendar to Entourage.... At wits' end here! Please advise...
I have a Mac at work which runs Microsoft Outlook 2001 client on an Exchange server. I have a calendar on this machine (stored locally, not on the server) which has a ton of reminders programmed into it - very useful! Now, at home I got a new Mac laptop with Entourage X. Is there any way to move all of the reminders (appointments) from my calendar on Outlook to my Entourage calendar? I've tried two things: 1) I exported the calendar info as a CSV text file, and then tried importing it in Entourage. The problem is that Entourage import function seems to think it's importing an Address...

SUMIF Formula Help
I want to sum a column from a different sheet using the date as the condition for the formula. I need a formula that will sum all the amounts that are equal to or less than a specific cell E1 (which would be the date). Store 1 is the first sheet and I have 99 sheets for 99 stores. I tried the following formula but got back -3.0559E-10 as the result. The result should have been $18,810.90. I am not sure what I am doing wrong. Any help will be greatly appreciated. =SUM(IF(('Store #1'!B1:B73<=E1),'Store #1'!E1:E73)) Store # Period Step Rent 1 04/01/2008 9...

field returns a value if at least one matching record is found
I had posted this question in the Forms thread, but that was probably the wrong place. TblPartNumInfo TblDefectRpt My query for PartNumInfo brings together lots of different tables including customers, contact info, status of jobs. A feature I need to add is a warning if at least one defect report exists for a given part number. It would be shown on the form as a conditional format of the text box where the part number is shown. So I would like to add a field to my query that would return some value (like the part#ID) if at least one Defect Report exists for a part#. I created a sepa...

Matching transactions
What are the "rules" for money's matching transactions? I find that it constantly mismatches items when the name is different and the amount is different. Is there a way to direct it to "only" match exact amaounts? I don't need it to attempt to match to similar entries. Money 2004 deluxe. Scott ...

Help with DST
Greetings, I would like to know if you have place all the DST patches on the clients, servers, and apps but have not run the Exchange calendar clean up tool against your mailboxes or public folders, will you have any issues come fall. Becase we only publish 6 months worth of calendars will the apps and tasks have the right DST infomation because of the new CDO dll? If not why? Also if there will be issues and I need to run the tool can I run it after April 1st and not have the users notice any changes? Thanks, D On Wed, 28 Mar 2007 07:56:06 -0700, DW <DW@discussions.microsoft....

Partial payment of a bill?
Is it possible to make a partial payment of a bill...which the balance stays as an open bill? Thanks See http://umpmfaq.info/faqdb.php?q=132 for some thoughts on this problem. "redruthann" <redruthann@discussions.microsoft.com> wrote in message news:A0FE488C-5752-413C-B983-1F486D548D03@microsoft.com... > Is it possible to make a partial payment of a bill...which the balance > stays > as an open bill? ...

MATCH search with text and numbers
I compiled a large database of city blocks in an area. Now I need to be able to pull information from a city block by entering a street address on that block. The database looks something like this: A B C D-E-F 1 From To Street Name Various info. regarding block 2 2000 2099 Webster 3 2100 2199 Webster 4 2200 2099 Webster 5 2000 2099 Clybourn 6 2100 2199 Clybourn 7 2200 2099 Clybourn I need to write up a formula so that when I write: A B 8 2134 Damen The formula searches to determine which range the number...

Need Help with Totals Query
I have a table with daily call records. The table includes fields for: Department; Name; Date of Call; Time of Day; Call Direction; Number Dialed; Locale; Length of Call. I created a query that will show calls lasting longer than 3 minutes for a given time period, for example calls between 10 AM and 12 PM. The query prompts the user for date(s). Now I need to add counts for: total calls; out-bound calls; and in-bound calls by user. I tried including a totals row in the query and adding a colum to count outbound calls, but I get the error that "This expression is typ...

isna HELP
Hi Having a nightmare trying to change the #n/a to 0 {zero} this is the formula =IF((AJ4="H")+(AM4="H"),"HH",FALSE) maybe it's obvious but I could do with a fresh pair of eyes on it. Many thanks Paul T =if(and(aj4="h",am4="h"),"hh",0) ******************* ~Anne Troy www.OfficeArticles.com "Paul T" <paul.thomas5@ntlworld.com> wrote in message news:3pbFe.4335$Aw4.2912@newsfe5-win.ntli.net... > Hi > > Having a nightmare trying to change the #n/a to 0 {zero} > > this is the formula > > =...

date & time formulas HELP !!
Hi - i have to find out how long did it take to solve a case. the formaul is (time completed) - (time created) my problem is when files are closed at midnight because of the format ... any ideas how to fix it??? Column A = time created Column B = time completed Column C = Duration (in hours only) timeCreated timeCompleted Duration 04/07/2010 15:00:00 04/07/2010 00:00:00 should be 9:00:00 Cheers, Mayte 04/07/2010 15:00:00 is bigger than 04/07/2010 00:00:00 Try 04/07/2010 23:59:59 in A1 and 00:00:01 in cell ...

SUMIF & Partial Strings (followup posting)
I couldn't post a follow up, so I've created a new message here... From: Jerry W. Lewis (post_a_reply@no_e-mail.com) Subject: Re: SUMIF & Partial Strings Date: 2003-01-27 02:14:06 PST Use... =SUMIF(A3:A29, "=*Alls*", B3:B29) or =SUMIF(A3:A29, "=*" & A52 & "*", B3:B29) where A52 equals "Alls" This way, if you have two entries that begin "Alls", they will be added together. Drew. --------------------------------------------------------------- From: Mike Copeland (mrcNOSPAM2323@cox.net) Date: 2003-01-27 20:58:31 PST ...

HELP: Lost my Product Key
Hello, I have Publisher 2002 but lost the product key. Is there anyway to retrieve it from another system on which Publisher was installed? Rich No, but you can call Microsoft and they will generate a new number for you. You'll apy for the long distance call but it only took about 10 minutes for me. "Rich S." <rstringer@provide.net> wrote in message news:e8yVIz3hDHA.1276@tk2msftngp13.phx.gbl... > Hello, > > I have Publisher 2002 but lost the product key. Is there anyway to retrieve > it from another system on which Publisher was installed? > > Ric...

One partial table, one master table
I've got a listing of 3000 personnel. Personnel in column A, supervisors in column B. In a separate worksheet, I have a listing of 400 personnel in column A. Same names in column A, sheet 1 as in column A, sheet 2, just a shorter listing in sheet 1.(smaller worksheet). Short list: Sheet 1 Master list: Sheet 2 How do I get EXCEL to compare sheet 1 with sheet 2, and fill in the appropriate supervisor in sheet 1? You can use VLOOKUP to find the supervisors for sheet 1 from sheet 2 In the column next to the personell in the short list try this formula =VLOOKUP(A1,Sheet...

Supressing select parameter dialog box
hi. A very minor bug really, but it's really annoying me. Can anyone tell me what can be done to stop the select pparametre message coming up every time the report starts? Obiously, I've had this before, but I can't find anyway round it this time. Some sort of code would proberly do it. (I've seen code that subpresses warnings, so I guess the same can be done here.) -- Message posted via http://www.accessmonster.com Remove the parameter from the query. Instead, use the WhereCondition of OpenReport. Here's a couple of examples: Print the record in the form at: h...

INDEX MATCH LARGE Ranking
I'm using data in one sheet to create rankings in another sheet. However, if there are duplicate values, it only returns the first matched value. For example...let's say im a teacher and am keeping track of my student's test grades. One sheet has the raw data (name and grade). The other sheet is ranking these students based on their grade, using the LARGE function, and matching these grades to students using INDEX MATCH. Now let's say two students got a 90 (A1=John and B1=90, A5=Joe and B5=90). It's returning John's name twice because he is at the top of the list....

Help with selecting data
Can someone correct the following expression so that my database comes up with a list of forms issued and the highest number issued for each? SELECT [ID], [Form_Number] & " " & [Ending_Number] FROM DocumentsIssued ORDER BY [Form_Number] & ", " & [Ending_Number]; Currently, the above is generating all the forms and all the ending numbers. It also includes forms that have not been issued with the Ending Number of 0. To be clear, here's a sample of what I get, followed by what I need: Form A 15 Form A 30 Form A 90 Form B 10 Form C 30 Form C 6...

balances don't match
Hi anyone have an idea how to resolve this: I have an investment account set up with an associated cash account...which is baqsically used as a checking account. It is setup with online update via Schwab. The actual balance of the account matches the balance on the "cash transactions" page of the account register. But my "net worth" report and the home page show the balance as significantly lower (thousands of dollars) and when my balance is low, it even shows negative... all the while the register still shows the accurate balance. any clues ? In microsoft.public....

HELP #14
I may have done something that I can't restore...I permanently deleted the "Saved Messages" from the Folder of Outlook. How can I restore? I have not turned off my computer, yet. Permanently deleted? So they're not in the Deleted Items folder? Any chance you have a backup of your data file somewhere? Otherwise...I can't think of anything you'll be able to do. -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the ...

Match words
I want to fill a cell with color if it contains a word that contai letters that I specify. For example words then end with letter "s". Wildcard search doesn't seem to work? What is the formula to use -- KH_G ----------------------------------------------------------------------- KH_GS's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3292 View this thread: http://www.excelforum.com/showthread.php?threadid=52741 use conditional formatting and formula is =RIGHT(A1)="s" click the format button and pattern and select the colour -- Reg...