 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / September 2007Muliple lookup in one cell
My data sample: aaaa-@345-1111.txt aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222",A2)),TRUE,FALSE) copy down  SignatureRegards Roger Govier > My data sample: > aaaa-@345-1111.txt [quoted text clipped - 3 lines] > > I want to return the cell that contains aaaa and 2222. Or just: =ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2)) To the OP: =Find() is case sensitive =Search() is not case sensitive > Hi Tim > [quoted text clipped - 13 lines] > > > > I want to return the cell that contains aaaa and 2222. SignatureDave Peterson Much neater, Dave!!! SignatureRegards Roger Govier > Or just: > =ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2)) [quoted text clipped - 20 lines] >> > >> > I want to return the cell that contains aaaa and 2222. My is much neater than Dave. > Much neater, Dave!!! > [quoted text clipped - 22 lines] > >> > > >> > I want to return the cell that contains aaaa and 2222. Until the 2222 appears before the aaaa. > My is much neater than Dave. > [quoted text clipped - 32 lines] > > > > > > Dave Peterson SignatureDave Peterson Give me a break. It still work even 2222 appears before the aaaa > Until the 2222 appears before the aaaa. > [quoted text clipped - 34 lines] > > > > > > > > Dave Peterson TM, I think this is just a difference in philosophy. Your formula =ISNUMBER(SEARCH("aaaa*2222",A1)) is so far the most elegant that could solve the OP's problem precisely as the OP presented it. The other formulas are longer, but more flexible, and may be useful to other people (or to the OP himself, if he didn't completely specify the range of cases). Just my 2¢. - David Hilberg > Give me a break. > [quoted text clipped - 38 lines] >>>>> >>>>> Dave Peterson Your search specification is for "aaaa" followed by any or no character(s) followed by "2222". If "2222" is not preceded by "aaaa" (or "AAAA", as SEARCH isn't case-sensitive) anywhere in the string, the formula will return False. Mark Lincoln On Sep 12, 10:38 am, Teethless mama <Teethlessm...@discussions.microsoft.com> wrote: > Give me a break. > [quoted text clipped - 44 lines] > > - Show quoted text - Did you even try it? > Give me a break. > [quoted text clipped - 42 lines] > > > > Dave Peterson SignatureDave Peterson This does not seem to return a cell, it returns a calculated value. > Hi Tim > [quoted text clipped - 9 lines] > > > > I want to return the cell that contains aaaa and 2222. Roger suggestion include the "copy down" note. Then you would look at that column to pick out the item that returned true. If you wanted to return the first value that matched your criteria, you could use this array formula: =INDEX(A2:A100, MATCH(TRUE,ISNUMBER(FIND("aaaa",A2:A100)*FIND("2222",A2:A100)),0)) or this version based on toothless mama's response: =INDEX(A2:A100,MATCH(TRUE,ISNUMBER(SEARCH("aaaa*2222",A2:A100)),0)) This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it correctly, excel will wrap curly brackets {} around your formula. (don't type them yourself.) Adjust the range to match--but you can only use the whole column in xl2007. > This does not seem to return a cell, it returns a calculated value. > [quoted text clipped - 15 lines] > > > > > > I want to return the cell that contains aaaa and 2222. SignatureDave Peterson I see I have not asked my question with all the details required. The data example is on one sheet. On another sheet I have 3 or more columns. The header and first data element to search/ find in columns B, C, ... are aaaa, bbbb The row data and second data element for a2, a3, a4, ... are 1111, 2222, 2345, ... I would like b2 to return the value from the first data sheet that has aaaa and 1111, c2 return bbbb and 1111, etc. > Roger suggestion include the "copy down" note. > [quoted text clipped - 36 lines] > > > > > > > > I want to return the cell that contains aaaa and 2222. If you don't get any responses, you may want to rephrase your question. I know that I don't understand it. > I see I have not asked my question with all the details required. > [quoted text clipped - 53 lines] > > > > Dave Peterson SignatureDave Peterson =ISNUMBER(SEARCH("aaaa*2222",A1)) > My data sample: > aaaa-@345-1111.txt [quoted text clipped - 3 lines] > > I want to return the cell that contains aaaa and 2222. This does not seem to return a cell, it returns a calculated value. > =ISNUMBER(SEARCH("aaaa*2222",A1)) > [quoted text clipped - 5 lines] > > > > I want to return the cell that contains aaaa and 2222. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.