IF or SUMIF to bring back text?

I am trying to match departments which have two different titles.  For 
example, there are 50 departments that are futher classified into 150 
sub-departments.  I do have the data showing departments and their 
corresponding sub-departments in two columns.  On another sheet there are 
only sub-departments and I need to assign the corresponding department name.  
I have tried IF and SumIf but neither works.   Basically, I need a formula to 
match the text and bring back the text for the sub-department.  
Thank you.
0
Utf
4/6/2010 7:14:21 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
972 Views

Similar Articles

[PageSpeed] 51

sounds like you need VLOOKUP

"Valerie" wrote:

> I am trying to match departments which have two different titles.  For 
> example, there are 50 departments that are futher classified into 150 
> sub-departments.  I do have the data showing departments and their 
> corresponding sub-departments in two columns.  On another sheet there are 
> only sub-departments and I need to assign the corresponding department name.  
> I have tried IF and SumIf but neither works.   Basically, I need a formula to 
> match the text and bring back the text for the sub-department.  
> Thank you.
0
Utf
4/6/2010 7:38:01 PM
Reply:

Similar Artilces:

Why is justified text alignment not working?
The justified text alignment is supposed to automatically left align the last line of a paragraph. Why is it not working - stretching out the last line when it should'nt be ? Did you try a carriage return at the end of the sentence? -- Don - Publisher 2000� Vancouver, USA "Karyn" <Karyn@discussions.microsoft.com> wrote in message news:2BAF9FE2-8721-4CED-A258-D817FDD4079B@microsoft.com... > > The justified text alignment is supposed to automatically left align the > last > line of a paragraph. Why is it not working - stretching out the...

problem import text file
hi, when i try to import text file to excel, i find that there are lots of =E2=96=A1 symbols on my spreadsheet. any idea how to get raid of them? thanks Hi Lynn CHAR(10) is inserted when you use Alt-Enter to force a line wrap. It is not "seen" in the cell it is entered in but will appear as a box in cells that reference it. You can use this macro to clean your data http://www.mvps.org/dmcritchie/excel/join.htm#trimall -- Regards Ron de Bruin http://www.rondebruin.nl "Lynn" <moley_cruz@yahoo.com.au> wrote in message news:1128239809.490793.207970@g44g2000cwa.go...

Disable automatic text wrap?
It appears that when one cuts and pastes from one Excel spreadsheet to another, it automatically assumes that you want to wrap text if the column is too narrow. Since I almost never want that, I have to go into the "format cells" area and uncheck "wrap text" every time. Is there any way to change the default treatment from "wrap text"? Does the copied cell have 'wrap text' set? Have you tried 'Paste Special'? "Staceyglow" wrote: > It appears that when one cuts and pastes from one Excel spreadsheet to > ano...

how do I create a macro of text in Outlook 2003?
I need to insert a short sentence in many of my emails in the subject line. I tried looking into creating a Macro via the Outlook Help instructions, but I don't know how to write code. It seems that 2007 has quickstarts for this purpose, but can anyone tell me how to do it in Outlook 2003? the term is QuickParts http://www.timeatlas.com/Email/Outlook/Reusing_Outlook_Text_and_Images Marie wrote: > I need to insert a short sentence in many of my emails in the subject line. > I tried looking into creating a Macro via the Outlook Help instructions, but > I don...

How to change color palette in 2007 BACK to 2003 version?
My company used Excel 2003 for several years, and relied on setting certain things to certain colors. We routinely used 10-15 of the stock 2003 colors for certain functions. now that we are migrating to 2007, we are very disappointed to find a completely different set of colors! Is there any way we can roll back to the 2003 color palette? I've seen a variety of software packages to create custom colors, etc., but I just want the old palette back! I need blue to be blue (not baby blue!) and green to be green (not pastel green!). It's very frustrating that Microsoft has chosen t...

How can I put 2 separates pages together for a front
I created a several single pages of a program I'm putting together. Now I would like to put some of these pages together and take advantage of the duplex printing capability of my printer. Is there anyway to do that short of cut, copy and paste. For only several single pages, copy and paste is probably the easiest way. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "iamrcc" <iamrcc@discussions.microsoft.com...

Separating Text strings.
Is it possible to separate a name in one cell such as John Smith in A1 to John in cell B1 and Smith in cell C1. I read that by entering =LEFT(A1,FIND("",A1)) into B1 and =MID(A1,FIND("",A1)+1,LEN(A1)) into C1 that would do it. However that returns �J� in cell B1 and �ohn Smith� in C1 Specifying the number of characters for the first string would not work on a list of names were the first string varies in length, so you need a formula that takes everything before the first space as the first string. Is it possible? -- Quaisne -------------------------------------------...

Selecting all text for formatting
I was just wondering if anyone knows of an easy way to select all the text in all text boxes in Publisher, so as the font can be formatted. I've just drawn a diagram (network schematic) with some 70 or 80 text boxes, and discovered that I need more space. I have tried hitting Ctrl-A but this won't let me change any font settings, I've also tried using "Ctrl-Click" to select only the text boxes and not the adjoining lines, also to no avail. The closest I've managed si the format paster which also pastes the borders/colours etc. of the text box itself. Publisher ...

Full-text indexing question
All, Hopefully this isn't a RIDICULOUSLY dumb or over-asked question, but in your opinion, how useful has full-text indexing been to you in terms of the the performance of your Exchange server? -Pair Depending on the amount of change, it can really put your Exchange server on its knees. Especially if you update the index every 15 minutes. I have used it on selected mailbox stores, but usually schedule it only to update once a day (after hours.) -- Jim McBee Blog - http://mostlyexchange.blogspot.com "TwistedPair" <twistedpair@mail.com> wrote in message news:...

Logical operators not working with text???
For some reason my comparison operators aren't working in my spreadsheets and I can't figure out why. I think this worked on another PC. What I want to do is have a row that increments a value if the entries are the same - really simple - Lets assume all the values in the A row are below: A B 1 - TTTT 0 2 - TTUU Formula Below 3 - TTUU 4 - VVVV 5 - WWW 6 - WWW In the B row I set B1 to zero and then type the following in cell 2: =IF(A2=A1,B1+0,B1+1) So if A2 (TTUU) is equivalent to A1 (TTTT) then don't increment, if it's false then it's a new record so increment....

How to append to a Text Box Without using MFC
I wish to append lines to a multiline edit box gor which i am trying to use but nowhere............. int iLength; iLength = strlen(sText) for(int iIndex=0;iIndex<iLength;iIndex++) { SendMessage(hWnd,WM_KEYDOWN,sText[iIndex],0); } Plz Help... Did you try something like SendMessage(hWnd, WM_SETTEXT, 0, (LPARAM)&sText[0]); -- Bjarne Nielsen "vikrant" <vikrant_kpr@hotmail.com> skrev i en meddelelse news:ubZDzs4pDHA.3612@TK2MSFTNGP11.phx.gbl... > I wish to append lines to a multiline edit box gor which i am trying to use > but nowhere............. > int...

Placein the value of a combo Box column into a text box
This should be easy! Here is the code... Private Sub Combo18_AfterUpdate() Me![SchoolID] = Me!Combo18.Column(1) Me![Counselor Name] = Me!Combo18.Column(2) End Sub The problem is that it will not accept the first line: Me![SchoolID] = Me!Combo18.Column(1) If I move it around it is perfectly willing to place the counselor's name into the correct text box, but for the schoolID it says I have to save the record first. Anyone have any idea why??? I've checked and rechecked spelling, properties, etc! Thanks for any help you can give. On Thu, 3 Jan 2008 07:52:02 -0800, Betsy <B...

sumif remove high/low values
Hello, I have a list of ten values. I would like to sum this list with the top 2 and bottom 2 values removed in the sum calculation. Any help would be great, ben -- Big Ben ------------------------------------------------------------------------ Big Ben's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6431 View this thread: http://www.excelforum.com/showthread.php?threadid=395038 Hi Ben, Try this =SUM(D1:D10)-SUMPRODUCT(--(LARGE(D1:D10,{1;2}))+(SMALL(D1:D10,{1;2}))) or if there will be 10 then you could use =SUMPRODUCT(--(LARGE(D1:D10,ROW(INDIRECT(&qu...

graphic behind text in Excel?
In an earlier version of Excel graphics could be placed behind text in a cell. On the drawing menu the icon that controlled this feature was a little dog. In Excel 2002 this does not appear to be a viable option. Does this feature strike a bell with anyone? Is there a way to accomplish this in 2002? I want to add a signature into a box and not have the signature overwrite the box. D, You can't put graphics in back of cells, except as a background picture, which is for the whole sheet (Format - Sheet - Background). It's limited, and doesn't print. What you're de...

Formating description text in email class
I have created an email activity workflow assembly and I am having trouble getting new line characters to work within the body of the email. For example: email em = new email(); em.description = "This should be the first line.\nThis should be the second line."; Using the code above, I would expect the the body of the email to be: This should be the first line. This should be the second line. Instead, the text runs together with just a space like this: This should be the first line. This should be the second line. I have tried other escape characters, but I have not found a co...

Conditional formatting of an entire row based on a cell text entry
Hi everyone, I would like to use conditional formatting on a table that is 8 columns wide and 50 rows high. What I would like to do is colour all rows that have an X in column D or E. This is what I tried The formula is =A1="x" This colours only the cells where there is an X. What I would like is the entire row or rows to be coloured. Thanks for your efforts Gilles Try setting to The formula is =OR($D1="X",$E1="X") The mixed reference will allow you to copy this to your entire table, keeping the references fixed to column D or E of the current row. &qu...

Shape with "in line with text" can't be found
When I insert a picture into Word as Word.Shape, I set the "Text Wrapping" as "In line with Text" by Word GUI, after that in programming, the shape can't be found in "Document.Shapes" collection any more. Does anyone know how to solve this problem? The picture will then be an InlineShape If you want the picture as an inline shape why don't you insert it as one. e.g. ActiveDocument.InlineShapes.AddPicture "D:\Path\Picture.jpg" -- <>>< ><<> ><<> <>>< ><<> <>>< ...

How to format in a cell that has both text and a formula
I have a cell with this formula ="James Cedric V. Sebua ("& ((TODAY()-B14)/365) & ")" The display is this James Cedric V. Sebua (5.55890410958904) And I want it to be this: James Cedric V. Sebua (5) Thanks Dave Hi Dave, Try: ="James Cedric V. Sebua ("& INT((TODAY()-B14)/365) & ")" -- Cheers macropod [Microsoft MVP - Word] "Dave" <dave@accessdatapros> wrote in message news:EA033D26-48C7-4D91-8855-621A76C8367D@microsoft.com... >I have a cell with this formula > > > =&...

Background Image Span Two Pagers (Front and Back)
In designing a booklet, I've got an image I want to span the front and back cover -- so that in a 4-page booklet, it's pages 1 and 4. I simply cannot figure out how to do this, even though some of the "canned" booklets/greeting cards do this. The front and back covers do not appear together in layout view. If I oversize the image on page 1, the printer just cuts it off and the back cover is completely blank. Any help on how to make this happen would be greatly appreciated. I'm using Publisher 2002. In your case you could simply create a landscape publication and...

superscript text in legend
I have created some superscript text in some of the column headings in my table, i.e., Gender[a], Education[b], with [a] and [b] being superscriptted. However, in the legend of my chart, the superscript text just appears regular size, so it looks like I have committed typos. Is there a way I can maintain the "superscriptness" of certain characters in my legend text? No. Unfortunately, when a chart uses a cell to title (or text box) it does not take the format with it. -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "hogon" <hogon@discus...

copying SUMIF statements
I'm trying to find out how to copy a SUMIF statement that I've made to add up values for specific accounts. I've developed the SUMIF formula for brand X, and now I want to be able to copy the formula for a different brand Y, without having to manually go into the formula and change every "X" reference to a "Y" reference. Any suggestions?... Have a look in the help index for INDIRECT -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com <bradyk819@gmail.com> wrote in message news:1194276913.282292.46110@v3g2000hsg.googlegroups...

Center Caption text
How can I center the text on the caption of a dialog window? Under "styles," it gives a "right-align text" but I don't see a center option. Thanks, Matt Matt, What you want to do is painting the non-client area yourself.You'll find articles here: http://www.codeproject.com/dialog/#Dialogs and here: http://www.codeguru.com/Cpp/W-D/dislog/ where http://www.codeguru.com/Cpp/W-D/dislog/titlebar/article.php/c1987/ might be of particular interest. Johan Rosengren Abstrakt Mekanik AB <bonmatt10@cs.com> a �crit dans le message de news:1105151591.539589.316...

Change table text column lookup list
I must add a column using vba and need to change the lookup list. Using: Set tdf = DB.TableDefs("TableName") Set fld = tdf.CreateField("FieldName", dbText, 50) tdf.Fields.Append fld tdf.Fields.Refresh The FieldName column need a lookup list whose Display Control is a combo box row source type is a value list row source consists of a list as in Item1;Item2;Itemn... Can this be done using VBA when the column is created. -- RobGMiller RobGMiller wrote: >I must add a column using vba and need to change the lookup list. > >Using: ...

text boxes #12
I have scanned a work sheet that i would normally hand write in, and put it on publisher, added text boxes so that i can now fill these work sheets out on my computer and print, however how can i hide the text boxes and go from one text box to the other using my tab key? -- Alan You can't use tab this way in Publisher. You can link all the text boxes and use ctrl+shift+enter to go from box to box. You only have to do this once. The next time you will automatically go to the next text box by clicking the overflow icon. You could put the worksheet on the Master Page and create ...

Importing text into a Text Box
C1:C7 Sheets("Page") contain details of the particular job. I am trying to write a macro to add a Text box on another sheet "Schedule" to include these details. Recording a macro of the first few operations, initially typing the text, I have then attempted to use the variable 'JobDetail' (defined elsewhere and used throughout the workbook) to replace the text in the recorded macro but it does not appear in the text box when the macro is run. Any suggestions for bringing into the text box C1, C2, C3 etc fro the other sheet? Thanks in anticipation. ...