vlookup for words content when there are duplicate values

Hi,

Can somebody help me here.
Thanks in advance.

When I vlookup Material 1 to Material 2 for the Project No, I get
the vlookup Project No. as 222-56 for all the duplicate Material
values.
How formula to use so that when there are duplicate values in
Material2, I want to have vlookup searching to match Project No.
containg "AA" as 1st choice ? (if no Project No. containing "AA", then
looks for its content "AB" and subsequently looks for "AC" if there is
no  "AB"). 


ColumnA		ColumnB		ColumnC
Material 2		Duplicate?	Project No.
224410		duplicate		222-AC
224410		duplicate		222-AB
224410		duplicate		222-AA
326544		no		110007


Material 1		Vlookup
224410		222-56
224410		222-56
224410		222-56
527770		#N/A



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

0
9/19/2003 4:52:33 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
738 Views

Similar Articles

[PageSpeed] 13

Reply:

Similar Artilces:

Linking a sheet's content into a Word document
Hi. I have a Word document and an Excel document. Some of the content of the Word document merely duplicates the content of the Excel document. What I would like to do is to set up the Word document so that it automatically retrieves the text from the Excel document. Similar to doing a '=D5' in an Excel spreadsheet, so that it just harvests the contents of a particular cell (in this case, D5) and transplants it to another place. Is there a way to implant text from the Excel spreadsheet (which is in another file) into the Word document, so that it automatically opens the Excel spre...

Difficulty with the property .Value of a Range
Hi, Is someone knows why this sub works well Sub test() Dim MyDate As Date, X MyDate = CDate("14/08/2008") '******************* X = Application.Match(CLng(LaDate), Range("A1:A25"), 0) '******************* End Sub And if i add the property ".value" to the object "Range", an error is generated. Sub test() Dim MyDate As Date, X MyDate = CDate("14/08/2008") ' French format -> French Version Excel. '******************* X = Application.Match(CLng(LaDate), Range("A1:A25").Value, 0) '******************* ...

Find whole word only in Outlook Advanced Find
I cannot seem to find an option to "find whole word only" in Outlook 2002 Advanced Find? Does it exsist? Was it removed? Can I do anything about this? Help Thank Cheryl I don't recall a whole word only option in Advanced Find... -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com OneNote Tips: 1note-tips-subscr...

Naming sheets from a cell value
Hi there, Thanks for reading my question. Is it possible to name a sheet from a cell value with in the sheet? i.e. I have 12 sheets, each sheet represents a month and the month value is in cell A1. Can I rename a sheet by refering to cell A1 instead of manually renaming the sheets to January, February etc. Many thanks Tony See http://www.mcgimpsey.com/excel/events/sheetnamefromcell.html In article <127785E3-01B4-49B1-9966-8D79F795EFA4@microsoft.com>, "Tony4X4" <Tony4X4@discussions.microsoft.com> wrote: > Hi there, > Thanks for reading my question. >...

How can i do to search any key word on Exchange 2000 address book
for example full_name,i have a user name 'michael_jackson' that is show on the global address book. i want my outlook 2000 client to use the key word 'chae' or 'ckso' to find out it from my exhcnage 2000 server Global address list Now , i can only use the key word 'michael' or 'jackson' to find it on the result list. but i hope i can use any key word to find it on the full_name. plz any one can help me Another one question. the search funtion of outlook address book what is the default role. i mean the search funtion how it find...

match to colums vlookup
Hello everyone. It kills me that I fogot this, I've been using Crystal and Access far too much. Now it eats me alive not being able to remember something so easy. I have a Customer in A2 and a Category in B2 and a Total Sales in C2, I have the same lay out in another spreadsheet. I need match the customer and category in the other spreadsheet and bring it back to the new spreadsheet. Please help this poor soul. You mean you want to bring back the total sales when the customer and category match?? If yes: =index(othersheet!$c$1:$c$10, match(1,(a2=othersheet!$a$1:$a$100)*(b2=o...

Retrieving MS Word document's author..
How would I go about retrieving the author ( and consequently, of course, the title, comments, etc :) ) of a MS Word document? I use CFileFind to search a directory for ..doc files. CFile supports FileName, DateCreated and somesuch, but I get the distinct feeling that I am going to need a more specialized class than CFile. Esp. since I want to be able to do the same for MS PowerPoint files, MS Excel documents, etc. :) much regard, Erik Erik, The author, title etc. is indeed not a file system thing, but rather embedded in the data of the file. You will want to check Word automation (and late...

Microsoft Word 2004 extreme lag.
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) I'm experiencing an extreme lag when I open, type, delete character in word document. <br><br>I'm using the latest Macbook, with Snow Leopard Version 10.6.1. The Word 2004 has been updated to version 11.5.7, the latest one. It has been having this problem even before the update when it was still version 11.3.8. <br><br>It took 3 minutes just to open a document. Whenever I type or delete something, it took 5 to 10 seconds to response. <br><br>I had tried quitting the program and reopen ...

This has to be a WORD bug with TextBox VBA and protection
Try this please: 1. Create new doc as a dotm (macro enabled template) 2. From the Developer tab, Insert a ActiveX textbox control 3. Create a TextBox1.change routine, and apply some simple code – just MsgBox TextBox1 will suffice 4. Turn on Protect Document with editing restrictions Filling in Forms 5. Switch off Design Mode and test an entry in the text box Works fine. 6. Now close and save the dotm 7. Double-click the dotm from explorer (opens a document based on the template) 8. Take the Option (in toolbar) to allow active content 9. Try the textbox entry Can’t en...

return records based on max value joined on another table 03-12-10
Hi , First, thanks Sloan and others who helped on another post on this issue. Not returning everything? --test data DECLARE @TABLE1 TABLE([STATE] CHAR(2),ZONE INT ,CHARGE DECIMAL(15,2) ) INSERT INTO @TABLE1 ([STATE],ZONE,CHARGE) VALUES ('FL','1','23.56'),('FL','2','45.65'),('FL','3','52.20'),('FL','4','6.87'),('FL','5','63.55'), ('FL','6','73.89'),('FL','7','33.87'),('FL','8','19.15')...

including blanks or text values in macro-entered formulas
When I try to use the following codes, I get error messages. How do get around the problem? Cells(2,2).Select ActiveCell.Formula = "=if(A1=1,"",1)" or Cells(2,2).Select ActiveCell.Formula = "=if(A1=1,"High","Low")" Thanks so much for any assistance Matthe -- Message posted from http://www.ExcelForum.com Just as in XL, quotation marks within a string need to be doubled (or use CHR(34)): ActiveCell.Formula = "=IF(A1=1,"""",1)" and ActiveCell.Formula = "=IF(A1=1,""High"",...

In Word How do I switch off auto date insertion please
How can I disable? ...

XIRR & non-Contiguous Values?
I know IRR works with non-continguous values, but I can't get XIRR to work. Here's what I'm doing: =XIRR((B1,B5), (A1,A5), 0.07) A B 1/21/2005 -750 - - - 12/31/2005 795 I get #VALUE! IRR works on the same cells, minus the date arguments of course. Bob bluepost22@yahoo.com wrote: > =XIRR((B1,B5), (A1,A5), 0.07) should those have colons between the cells? like B1:B5 In this case I only have two dates and two values which are not located in adjacent cells in the spreadsheet. This is supposed to work in IRR, and it does, but I can't get it...

Duplicate Z and ZZ reports
I am getting duplicate shift closing reports from both of my registers. When I view them in the journal, there is one regular Z report, and one with all zeros. There are two ZZ reports also. The cashiers are not z'ing twice as I have tried this myself. With six shifts per day this is getting crazy! Any suggestions? Thanks so much. We had this happen once with version 1.2 What version are you running? "Target" <Target@discussions.microsoft.com> wrote in message news:B5CF6FFA-1621-4F1E-9359-0677EF6464B4@microsoft.com... >I am getting duplicate shift closing report...

Problems with Opening Mail Merge from Word
I'm trying to open a mail merge document I saved and when I try it freezes up. It begins to open and then opens a blank Excel spreadsheet and lists the following message along the status bar: "Waiting for Microsoft Excel to accept DDE Commands" Not sure what is going on and what I can do to fix this. The information in the following threads may help: http://groups.google.com/groups?&threadm=1f2c01c2ef17%246f4d2c10%24a301280a%40phx.gbl http://groups.google.com/groups?&threadm=OP%24GmbnDDHA.392%40TK2MSFTNGP12.phx.gbl Mario wrote: > I'm trying to open a m...

Nested formular to give a word answer based on 2 cells
I have a s/s which I want to produce a statement based on 2 cells G170 is a cell that contails a sum (if cells above are filled it it will add them up) I170 contains a cost if G170 & i170 are blank then do nothing If G170 is greater than 0 and I170 is greater than 0 then again do nothing but if G170 is greater than 0 and I170 is = < 0 then "No Charge Made" Can anyone help all I get is FALSE I have tried =if(g170<=0, and (i170=<0,No Charge made","")) Hi, =IF(AND(G170="",I170=""),"",IF(AND(G170>0,I170...

Custom function returning VALUE error
Hi, I am trying to construct a simple function to calculate distance between 2 points on Carthesian plane. The function I wrote: Function FieldsXY(startx, starty, endx, endy) FieldsXY = Application.SQRT((endx - startx) ^ 2 + (endy - starty) ^ 2) End Function The only result I'm getting is #VALUE! error. I tried to declare all arguments as integer, didn't help. Help would be greatly appreciated, as I'm no expert in this :). regards, Alex -- alex.k ------------------------------------------------------------------------ alex.k's Profile: http://www.excelforum.com/member.p...

Encrypt Content In XML
I will have security rights stored in XML data, and I just need the content of elements and/or attributes to be encrypted. When the dotnet application reads in the information, it would run a simple decryption on the data. I have one routine that does this, except is uses hexadecimal characters, so I need it to be valid characters for xml. Any routines out there to do this, encrypt and decrypt data that can be used in XML? Derek ...

Find proper value.
http://af2000.zoto.com/user/image_detail/IMG.0.b33e511490e9c3101e3a277c6047ea54-_CAT.0/date_uploaded-desc/0-30 Please see enclosed picture, (click over the link above) I have those yellow tables as a reference, I need excel to give me those data on blue table base on given numbers in the pink table. Thanks in advance for your future comments. ...

Using a VLookup "type" reference but the result is the CELL not th
I'm using a VLOOKUP to help me select a date range from another work sheet. ie 6/01/04 VLOOKs up a date on another sheet and it's corresponding revenue number. The same is applied to 6/30/04. I'm trying to sum the revenue in the cells (days 6/01-6/30) via the VLOOKUP formula, but the VLOOKUP only returns the value and not the cell location. I need the cell locations to sum the revenue numbers in between. ie. the revenue numbers between B4 and B10. Any help would be greatly appreciated Hi try the following =SUMPRODUCT(--(A1:A100>=DATE(2004,6,1)),--(A1:A100<=DATE(2004,6,1)...

Lookup value based on nearest date
I have a table of rates which occur on certain dates (one a month). It has two fields Rates.Date and Rates.Value I then another table where each record has a date in it, and I need to find the Value from Rates at the matching date, OR at the nearest date if there is not an exact match. It is possible that the date I need to lookup is before or after the range of dates in my Rates table. Is there a nice elegant way of doing this? Carol I believe both of those fieldnames ("Date" and "Value") are reserved words in Access. I'm certain about the first and confident ...

how to save word doc on usb flash card?
How do I save a doc in word from my PC desktop using a usb flash card (sony usb 2.0) 128M? what exact steps do I take to transfer this to my mac laptop (have word for mac) , work on the document there, then use the card to save changes on the apple and load them on to my pc desktop (and vice-versa). many thanks for a great forum. I'm not sure if it's advisable to save Word files directly to a USB stick. I think there's still a discussion going on (saving directly to floppy disks can cause some trouble, so don't do it), so for the time being, I'd recommend saving yo...

dyanmic picklist values
OK so I have set up a dynamic pick list but have run into a minor problem. I would like my second list to default to a unassigned or blank value, no matter what value is chosen on the first list? I have tried to update my values by adding a blank value at the start of my values iStartIndex = 0; iEndIndex = 0; iStartIndex = 35; iEndIndex = 39; This however does nothing, and since I can not set a blank value in the picklist, I am stuck. Any ideas or suggestions??? Denzel, You should add a value to you SubPicklist and call it "Unassigned" then reference that value in the Ca...

duplicates error after 2 years- why?
Question: Why would I get an error duplicate message after running this database for 2+ years? At this point, I cannot add additional data without receiving an error message. I have recieved the followng error message from a manufacturing database I created, and has been in use since 2008..."The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entires."...

How 2 enter a value in the Status Bar?
Hello, I'm using excel 2000. I have a test formula: =IF($N$37=$O$38,"OK","ERROR") I would like to use one of the boxes in the status bar to display the result. I have 8 of them, from L to R; 1. ? 2. SUM (or other f()) 3. ? 4. CAPS 5. NUM 6. ? 7. OVR 8. ? Is it possible to put the formula in one of these boxes, or in another place in the status bar? I need the result to be visible at all the times. Thank you in advance for your help, Manuel Hi Manual AFAIK you can't change this statusbar the way you want -- Regards Frank Kabel Frankfurt, Germany Manuel Dav...