|
Muliple lookup in one cell
|
Muliple lookup in one cell
Hi Tim
One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. |
Muliple lookup in one cell
Or just:
=ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2)) To the OP: =Find() is case sensitive =Search() is not case sensitive Roger Govier wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. -- Dave Peterson |
Muliple lookup in one cell
=ISNUMBER(SEARCH("aaaa*2222",A1))
"TimD" wrote: My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. |
Muliple lookup in one cell
Much neater, Dave!!!
-- Regards Roger Govier "Dave Peterson" wrote in message ... Or just: =ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2)) To the OP: =Find() is case sensitive =Search() is not case sensitive Roger Govier wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. -- Dave Peterson |
Muliple lookup in one cell
My is much neater than Dave.
"Roger Govier" wrote: Much neater, Dave!!! -- Regards Roger Govier "Dave Peterson" wrote in message ... Or just: =ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2)) To the OP: =Find() is case sensitive =Search() is not case sensitive Roger Govier wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. -- Dave Peterson |
Muliple lookup in one cell
Until the 2222 appears before the aaaa.
Teethless mama wrote: My is much neater than Dave. "Roger Govier" wrote: Much neater, Dave!!! -- Regards Roger Govier "Dave Peterson" wrote in message ... Or just: =ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2)) To the OP: =Find() is case sensitive =Search() is not case sensitive Roger Govier wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. -- Dave Peterson -- Dave Peterson |
Muliple lookup in one cell
Give me a break.
It still work even 2222 appears before the aaaa "Dave Peterson" wrote: Until the 2222 appears before the aaaa. Teethless mama wrote: My is much neater than Dave. "Roger Govier" wrote: Much neater, Dave!!! -- Regards Roger Govier "Dave Peterson" wrote in message ... Or just: =ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2)) To the OP: =Find() is case sensitive =Search() is not case sensitive Roger Govier wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. -- Dave Peterson -- Dave Peterson |
Muliple lookup in one cell
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 Teethless mama wrote: Give me a break. It still work even 2222 appears before the aaaa "Dave Peterson" wrote: Until the 2222 appears before the aaaa. Teethless mama wrote: My is much neater than Dave. "Roger Govier" wrote: Much neater, Dave!!! -- Regards Roger Govier "Dave Peterson" wrote in message ... Or just: =ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2)) To the OP: =Find() is case sensitive =Search() is not case sensitive Roger Govier wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. -- Dave Peterson -- Dave Peterson |
Muliple lookup in one cell
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 wrote: Give me a break. It still work even 2222 appears before the aaaa "Dave Peterson" wrote: Until the 2222 appears before the aaaa. Teethless mama wrote: My is much neater than Dave. "Roger Govier" wrote: Much neater, Dave!!! -- Regards Roger Govier "Dave Peterson" wrote in message ... Or just: =ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2)) To the OP: =Find() is case sensitive =Search() is not case sensitive Roger Govier wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. -- Dave Peterson -- Dave Peterson- Hide quoted text - - Show quoted text - |
Muliple lookup in one cell
Did you even try it?
Teethless mama wrote: Give me a break. It still work even 2222 appears before the aaaa "Dave Peterson" wrote: Until the 2222 appears before the aaaa. Teethless mama wrote: My is much neater than Dave. "Roger Govier" wrote: Much neater, Dave!!! -- Regards Roger Govier "Dave Peterson" wrote in message ... Or just: =ISNUMBER(FIND("aaaa",A2)*FIND("2222",A2)) To the OP: =Find() is case sensitive =Search() is not case sensitive Roger Govier wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Muliple lookup in one cell
This does not seem to return a cell, it returns a calculated value.
"Roger Govier" wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. |
Muliple lookup in one cell
This does not seem to return a cell, it returns a calculated value.
"Teethless mama" wrote: =ISNUMBER(SEARCH("aaaa*2222",A1)) "TimD" wrote: My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. |
Muliple lookup in one cell
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("222 2",A2:A100)),0)) or this version based on toothless mama's response: =INDEX(A2:A100,MATCH(TRUE,ISNUMBER(SEARCH("aaaa*22 22",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. TimD wrote: This does not seem to return a cell, it returns a calculated value. "Roger Govier" wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. -- Dave Peterson |
Muliple lookup in one cell
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. "Dave Peterson" wrote: 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("222 2",A2:A100)),0)) or this version based on toothless mama's response: =INDEX(A2:A100,MATCH(TRUE,ISNUMBER(SEARCH("aaaa*22 22",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. TimD wrote: This does not seem to return a cell, it returns a calculated value. "Roger Govier" wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. -- Dave Peterson |
Muliple lookup in one cell
If you don't get any responses, you may want to rephrase your question. I know
that I don't understand it. TimD wrote: 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. "Dave Peterson" wrote: 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("222 2",A2:A100)),0)) or this version based on toothless mama's response: =INDEX(A2:A100,MATCH(TRUE,ISNUMBER(SEARCH("aaaa*22 22",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. TimD wrote: This does not seem to return a cell, it returns a calculated value. "Roger Govier" wrote: Hi Tim One way =IF(ISNUMBER(FIND("aaaa",A2))*ISNUMBER(FIND("2222" ,A2)),TRUE,FALSE) copy down -- Regards Roger Govier "TimD" wrote in message ... My data sample: aaaa-*5#8-2222.zip bbbb-0987-2345.dat bbbb-*458-2222.xls I want to return the cell that contains aaaa and 2222. -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 08:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com