| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Index match to return all values
Dear all,
I have in : - column A, a list of items/jobs : Batching plant TT1 Eagle Tana Tran 1 unit MSO4500 Batching system - MHw industrial Dismantling plant - p30/09/03 stanwell - spo tr122919 Dismantling plant - p30/09/03 stanwell - inv no : 03550 1 unit Gen set S25 1 unit batching plant 1 unit Tanaka indicator 1 unit plant fabrication Add mhw industrial automation 1 turbo gp (or-6904) for wheel loader Safety platform, water meter stand, piping, crusher run Add sd mhw industrial auto s/b Overhaul wheel loader 938f Alkon batching system Alkon batching system Mhw's computer control system - column B, the lookup value , say, 'Batching' I would want in column C, the lookup result that should return all incidence of the word Batching appearing in Column A i.e : -column C, result should show : Batching plant TT1 Eagle Tana Tran Batching system - MHw industrial 1 unit batching plant Alkon batching system Alkon batching system I have used this formula in C5, =INDEX($A$5:$A$36,(MATCH(FALSE,ISERROR(SEARCH("*"& B5&"*",$A$5:$A$36)), 0))) entered as array (Ctrl+Shift+Enter) and manage to return the first found result 'Batching plant TT1 Eagle Tana Tran' but were unable to find all the rest of the intended results, preferably listed in separate rows. Any suggestion or help on this is greatly appreciated. Thanks heaps ! Everyone. SauQ |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
Hi,
Suppose your data is in F4:G10 and you type batching in cell A10. In cell B10, you can use the following array formula (Ctrl+Shift+Enter): =IF(ISERROR(INDEX($F$4:$G$10,SMALL(IF(ISNUMBER(SEA RCH($A$10,$F$4:$F$10,1)),ROW($F$1:$F$7)),ROW(1:1)) ,2)),"",INDEX($F$4:$G$10,SMALL(IF(ISNUMBER(SEARCH( $A$10,$F$4:$F$10,1)),ROW($F$1:$F$7)),ROW(1:1)),2)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "SauQ" wrote in message ... Dear all, I have in : - column A, a list of items/jobs : Batching plant TT1 Eagle Tana Tran 1 unit MSO4500 Batching system - MHw industrial Dismantling plant - p30/09/03 stanwell - spo tr122919 Dismantling plant - p30/09/03 stanwell - inv no : 03550 1 unit Gen set S25 1 unit batching plant 1 unit Tanaka indicator 1 unit plant fabrication Add mhw industrial automation 1 turbo gp (or-6904) for wheel loader Safety platform, water meter stand, piping, crusher run Add sd mhw industrial auto s/b Overhaul wheel loader 938f Alkon batching system Alkon batching system Mhw's computer control system - column B, the lookup value , say, 'Batching' I would want in column C, the lookup result that should return all incidence of the word Batching appearing in Column A i.e : -column C, result should show : Batching plant TT1 Eagle Tana Tran Batching system - MHw industrial 1 unit batching plant Alkon batching system Alkon batching system I have used this formula in C5, =INDEX($A$5:$A$36,(MATCH(FALSE,ISERROR(SEARCH("*"& B5&"*",$A$5:$A$36)), 0))) entered as array (Ctrl+Shift+Enter) and manage to return the first found result 'Batching plant TT1 Eagle Tana Tran' but were unable to find all the rest of the intended results, preferably listed in separate rows. Any suggestion or help on this is greatly appreciated. Thanks heaps ! Everyone. SauQ |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
Hi Ashish
The formula return "" i.e blank. I have checked and rechecked that I have entered them correctly but no luck. Any ideas/solutions? Many thanks. SauQ Ashish Mathur wrote: Hi, Suppose your data is in F4:G10 and you type batching in cell A10. In cell B10, you can use the following array formula (Ctrl+Shift+Enter): =IF(ISERROR(INDEX($F$4:$G$10,SMALL(IF(ISNUMBER(SEA RCH($A$10,$F$4:$F$10,1)),ROW($F$1:$F$7)),ROW(1:1)) ,2)),"",INDEX($F$4:$G$10,SMALL(IF(ISNUMBER(SEARCH( $A$10,$F$4:$F$10,1)),ROW($F$1:$F$7)),ROW(1:1)),2)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "SauQ" wrote in message ... Dear all, I have in : - column A, a list of items/jobs : Batching plant TT1 Eagle Tana Tran 1 unit MSO4500 Batching system - MHw industrial Dismantling plant - p30/09/03 stanwell - spo tr122919 Dismantling plant - p30/09/03 stanwell - inv no : 03550 1 unit Gen set S25 1 unit batching plant 1 unit Tanaka indicator 1 unit plant fabrication Add mhw industrial automation 1 turbo gp (or-6904) for wheel loader Safety platform, water meter stand, piping, crusher run Add sd mhw industrial auto s/b Overhaul wheel loader 938f Alkon batching system Alkon batching system Mhw's computer control system - column B, the lookup value , say, 'Batching' I would want in column C, the lookup result that should return all incidence of the word Batching appearing in Column A i.e : -column C, result should show : Batching plant TT1 Eagle Tana Tran Batching system - MHw industrial 1 unit batching plant Alkon batching system Alkon batching system I have used this formula in C5, =INDEX($A$5:$A$36,(MATCH(FALSE,ISERROR(SEARCH("*"& B5&"*",$A$5:$A$36)), 0))) entered as array (Ctrl+Shift+Enter) and manage to return the first found result 'Batching plant TT1 Eagle Tana Tran' but were unable to find all the rest of the intended results, preferably listed in separate rows. Any suggestion or help on this is greatly appreciated. Thanks heaps ! Everyone. SauQ |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
Hi,
Did you array enter (Ctrl+Shift+Enter) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "SauQ" wrote in message ... Hi Ashish The formula return "" i.e blank. I have checked and rechecked that I have entered them correctly but no luck. Any ideas/solutions? Many thanks. SauQ Ashish Mathur wrote: Hi, Suppose your data is in F4:G10 and you type batching in cell A10. In cell B10, you can use the following array formula (Ctrl+Shift+Enter): =IF(ISERROR(INDEX($F$4:$G$10,SMALL(IF(ISNUMBER(SEA RCH($A$10,$F$4:$F$10,1)),ROW($F$1:$F$7)),ROW(1:1)) ,2)),"",INDEX($F$4:$G$10,SMALL(IF(ISNUMBER(SEARCH( $A$10,$F$4:$F$10,1)),ROW($F$1:$F$7)),ROW(1:1)),2)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "SauQ" wrote in message ... Dear all, I have in : - column A, a list of items/jobs : Batching plant TT1 Eagle Tana Tran 1 unit MSO4500 Batching system - MHw industrial Dismantling plant - p30/09/03 stanwell - spo tr122919 Dismantling plant - p30/09/03 stanwell - inv no : 03550 1 unit Gen set S25 1 unit batching plant 1 unit Tanaka indicator 1 unit plant fabrication Add mhw industrial automation 1 turbo gp (or-6904) for wheel loader Safety platform, water meter stand, piping, crusher run Add sd mhw industrial auto s/b Overhaul wheel loader 938f Alkon batching system Alkon batching system Mhw's computer control system - column B, the lookup value , say, 'Batching' I would want in column C, the lookup result that should return all incidence of the word Batching appearing in Column A i.e : -column C, result should show : Batching plant TT1 Eagle Tana Tran Batching system - MHw industrial 1 unit batching plant Alkon batching system Alkon batching system I have used this formula in C5, =INDEX($A$5:$A$36,(MATCH(FALSE,ISERROR(SEARCH("*"& B5&"*",$A$5:$A$36)), 0))) entered as array (Ctrl+Shift+Enter) and manage to return the first found result 'Batching plant TT1 Eagle Tana Tran' but were unable to find all the rest of the intended results, preferably listed in separate rows. Any suggestion or help on this is greatly appreciated. Thanks heaps ! Everyone. SauQ |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
Yup, I array entered (Ctrl+Shift+Enter)
Still returns "", ie. blank. Many thanks, Ashish. SauQ On Jun 5, 12:55*pm, "Ashish Mathur" wrote: Hi, Did you array enter (Ctrl+Shift+Enter) -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "SauQ" wrote in message ... Hi Ashish The formula return "" i.e blank. I have checked and rechecked that I have entered them correctly but no luck. Any ideas/solutions? Many thanks. SauQ Ashish Mathur wrote: Hi, Suppose your data is in F4:G10 and you type batching in cell A10. *In cell B10, you can use the following array formula (Ctrl+Shift+Enter): =IF(ISERROR(INDEX($F$4:$G$10,SMALL(IF(ISNUMBER(SEA RCH($A$10,$F$4:$F$10,1)),*ROW($F$1:$F$7)),ROW(1:1) ),2)),"",INDEX($F$4:$G$10,SMALL(IF(ISNUMBER(SEARCH (*$A$10,$F$4:$F$10,1)),ROW($F$1:$F$7)),ROW(1:1)),2 )) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "SauQ" wrote in message .... Dear all, I have in : - column A, a list of items/jobs : Batching plant TT1 Eagle Tana Tran 1 unit MSO4500 Batching system - MHw industrial Dismantling plant - p30/09/03 stanwell - spo tr122919 Dismantling plant - p30/09/03 stanwell - inv no : 03550 1 unit Gen set S25 1 unit batching plant 1 unit Tanaka indicator 1 unit plant fabrication Add mhw industrial automation 1 turbo gp (or-6904) for wheel loader Safety platform, water meter *stand, piping, crusher run Add sd mhw industrial auto s/b Overhaul wheel loader 938f Alkon batching system Alkon batching system Mhw's computer control system - column B, the lookup value , say, 'Batching' I would want in column C, the lookup result that should return all incidence of the word Batching appearing in Column A i.e : -column C, result should show : Batching plant TT1 Eagle Tana Tran Batching system - MHw industrial 1 unit batching plant Alkon batching system Alkon batching system I have used this formula in C5, =INDEX($A$5:$A$36,(MATCH(FALSE,ISERROR(SEARCH("*"& B5&"*",$A$5:$A$36)), 0))) entered as array (Ctrl+Shift+Enter) and manage to return the first found result 'Batching plant TT1 Eagle Tana Tran' but were unable to find all the rest of the intended results, preferably listed in separate rows. Any suggestion or help on this is greatly appreciated. Thanks heaps ! Everyone. SauQ- Hide quoted text - - Show quoted text - |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
Hi,
If you wish, you may mail me the file at ask(at)ashishmathur(dot)com. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "SauQ" wrote in message ... Yup, I array entered (Ctrl+Shift+Enter) Still returns "", ie. blank. Many thanks, Ashish. SauQ On Jun 5, 12:55 pm, "Ashish Mathur" wrote: Hi, Did you array enter (Ctrl+Shift+Enter) -- Regards, Ashish Mathur Microsoft Excel MVPwww.ashishmathur.com "SauQ" wrote in message ... Hi Ashish The formula return "" i.e blank. I have checked and rechecked that I have entered them correctly but no luck. Any ideas/solutions? Many thanks. SauQ Ashish Mathur wrote: Hi, Suppose your data is in F4:G10 and you type batching in cell A10. In cell B10, you can use the following array formula (Ctrl+Shift+Enter): =IF(ISERROR(INDEX($F$4:$G$10,SMALL(IF(ISNUMBER(SEA RCH($A$10,$F$4:$F$10,1)),*ROW($F$1:$F$7)),ROW(1:1) ),2)),"",INDEX($F$4:$G$10,SMALL(IF(ISNUMBER(SEARCH (*$A$10,$F$4:$F$10,1)),ROW($F$1:$F$7)),ROW(1:1)),2 )) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "SauQ" wrote in message ... Dear all, I have in : - column A, a list of items/jobs : Batching plant TT1 Eagle Tana Tran 1 unit MSO4500 Batching system - MHw industrial Dismantling plant - p30/09/03 stanwell - spo tr122919 Dismantling plant - p30/09/03 stanwell - inv no : 03550 1 unit Gen set S25 1 unit batching plant 1 unit Tanaka indicator 1 unit plant fabrication Add mhw industrial automation 1 turbo gp (or-6904) for wheel loader Safety platform, water meter stand, piping, crusher run Add sd mhw industrial auto s/b Overhaul wheel loader 938f Alkon batching system Alkon batching system Mhw's computer control system - column B, the lookup value , say, 'Batching' I would want in column C, the lookup result that should return all incidence of the word Batching appearing in Column A i.e : -column C, result should show : Batching plant TT1 Eagle Tana Tran Batching system - MHw industrial 1 unit batching plant Alkon batching system Alkon batching system I have used this formula in C5, =INDEX($A$5:$A$36,(MATCH(FALSE,ISERROR(SEARCH("*"& B5&"*",$A$5:$A$36)), 0))) entered as array (Ctrl+Shift+Enter) and manage to return the first found result 'Batching plant TT1 Eagle Tana Tran' but were unable to find all the rest of the intended results, preferably listed in separate rows. Any suggestion or help on this is greatly appreciated. Thanks heaps ! Everyone. SauQ- Hide quoted text - - Show quoted text - |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
Dear all
Just an after thought, would it be possible to convert the formula into a UDF? Thanks heaps! everyone SauQ |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
On Thu, 4 Jun 2009 18:34:29 -0700 (PDT), SauQ wrote:
Dear all, I have in : - column A, a list of items/jobs : Batching plant TT1 Eagle Tana Tran 1 unit MSO4500 Batching system - MHw industrial Dismantling plant - p30/09/03 stanwell - spo tr122919 Dismantling plant - p30/09/03 stanwell - inv no : 03550 1 unit Gen set S25 1 unit batching plant 1 unit Tanaka indicator 1 unit plant fabrication Add mhw industrial automation 1 turbo gp (or-6904) for wheel loader Safety platform, water meter stand, piping, crusher run Add sd mhw industrial auto s/b Overhaul wheel loader 938f Alkon batching system Alkon batching system Mhw's computer control system - column B, the lookup value , say, 'Batching' I would want in column C, the lookup result that should return all incidence of the word Batching appearing in Column A i.e : -column C, result should show : Batching plant TT1 Eagle Tana Tran Batching system - MHw industrial 1 unit batching plant Alkon batching system Alkon batching system I have used this formula in C5, =INDEX($A$5:$A$36,(MATCH(FALSE,ISERROR(SEARCH("*" &B5&"*",$A$5:$A$36)), 0))) entered as array (Ctrl+Shift+Enter) and manage to return the first found result 'Batching plant TT1 Eagle Tana Tran' but were unable to find all the rest of the intended results, preferably listed in separate rows. Any suggestion or help on this is greatly appreciated. Thanks heaps ! Everyone. SauQ There may be simpler ways, but the following **array** formula seems to work. Just fill down further than you need to. It will return blanks after you've returned all the matches. =IF(ROW()=(COUNTIF(Jobs,"*"&LookupValue&"*")+ MIN(ROW(Jobs))),"",INDEX(Jobs,1-MIN(ROW(Jobs))+ LARGE(ISNUMBER(SEARCH(LookupValue,Jobs))* ROW(Jobs),MIN(ROW(Jobs))-ROW()+COUNTIF( Jobs,"*"&LookupValue&"*")))) Note that I used named ranges. But you could substitute absolute references for the named ranges, and it should work also. =IF(ROW()=(COUNTIF($A$5:$A$36,"*"&$B$5&"*")+ MIN(ROW($A$5:$A$36))),"",INDEX($A$5:$A$36,1-MIN(ROW($A$5:$A$36))+ LARGE(ISNUMBER(SEARCH($B$5,$A$5:$A$36))* ROW($A$5:$A$36),MIN(ROW($A$5:$A$36))-ROW()+COUNTIF( $A$5:$A$36,"*"&$B$5&"*")))) --ron |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
Ron 's and Biff 's solutions works like a charm.
Thanks a zillion guys... Ashish, Biff & Ron. Heart felt appreciation for your effort and time in helping to solve this. Rgds SauQ Ron Rosenfeld wrote: On Thu, 4 Jun 2009 18:34:29 -0700 (PDT), SauQ wrote: Dear all, I have in : - column A, a list of items/jobs : Batching plant TT1 Eagle Tana Tran 1 unit MSO4500 Batching system - MHw industrial Dismantling plant - p30/09/03 stanwell - spo tr122919 Dismantling plant - p30/09/03 stanwell - inv no : 03550 1 unit Gen set S25 1 unit batching plant 1 unit Tanaka indicator 1 unit plant fabrication Add mhw industrial automation 1 turbo gp (or-6904) for wheel loader Safety platform, water meter stand, piping, crusher run Add sd mhw industrial auto s/b Overhaul wheel loader 938f Alkon batching system Alkon batching system Mhw's computer control system - column B, the lookup value , say, 'Batching' I would want in column C, the lookup result that should return all incidence of the word Batching appearing in Column A i.e : -column C, result should show : Batching plant TT1 Eagle Tana Tran Batching system - MHw industrial 1 unit batching plant Alkon batching system Alkon batching system I have used this formula in C5, =INDEX($A$5:$A$36,(MATCH(FALSE,ISERROR(SEARCH("*" &B5&"*",$A$5:$A$36)), 0))) entered as array (Ctrl+Shift+Enter) and manage to return the first found result 'Batching plant TT1 Eagle Tana Tran' but were unable to find all the rest of the intended results, preferably listed in separate rows. Any suggestion or help on this is greatly appreciated. Thanks heaps ! Everyone. SauQ There may be simpler ways, but the following **array** formula seems to work. Just fill down further than you need to. It will return blanks after you've returned all the matches. =IF(ROW()=(COUNTIF(Jobs,"*"&LookupValue&"*")+ MIN(ROW(Jobs))),"",INDEX(Jobs,1-MIN(ROW(Jobs))+ LARGE(ISNUMBER(SEARCH(LookupValue,Jobs))* ROW(Jobs),MIN(ROW(Jobs))-ROW()+COUNTIF( Jobs,"*"&LookupValue&"*")))) Note that I used named ranges. But you could substitute absolute references for the named ranges, and it should work also. =IF(ROW()=(COUNTIF($A$5:$A$36,"*"&$B$5&"*")+ MIN(ROW($A$5:$A$36))),"",INDEX($A$5:$A$36,1-MIN(ROW($A$5:$A$36))+ LARGE(ISNUMBER(SEARCH($B$5,$A$5:$A$36))* ROW($A$5:$A$36),MIN(ROW($A$5:$A$36))-ROW()+COUNTIF( $A$5:$A$36,"*"&$B$5&"*")))) --ron |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
On Thu, 4 Jun 2009 21:28:57 -0700 (PDT), SauQ wrote:
Ron 's and Biff 's solutions works like a charm. Thanks a zillion guys... Ashish, Biff & Ron. Heart felt appreciation for your effort and time in helping to solve this. Rgds SauQ You're welcome. Thanks for the feedback. --ron |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
Try this...
Data in the range A2:A18 B2 = batching Enter this formula in C2: =COUNTIF(A2:A18,"*"&B2&"*") That will return the count of records that meet the criteria. Enter this array formula** in B3: =IF(ROWS(B$3:B3)<=C$2,INDEX(A$2:A$18,SMALL(IF(ISNU MBER(SEARCH(B$2,A$2:A$18)),ROW(A$2:A$18)),ROWS(B$3 :B3))-ROW(A$2)+1),"") ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Copy down until you get blanks -- Biff Microsoft Excel MVP "SauQ" wrote in message ... Dear all, I have in : - column A, a list of items/jobs : Batching plant TT1 Eagle Tana Tran 1 unit MSO4500 Batching system - MHw industrial Dismantling plant - p30/09/03 stanwell - spo tr122919 Dismantling plant - p30/09/03 stanwell - inv no : 03550 1 unit Gen set S25 1 unit batching plant 1 unit Tanaka indicator 1 unit plant fabrication Add mhw industrial automation 1 turbo gp (or-6904) for wheel loader Safety platform, water meter stand, piping, crusher run Add sd mhw industrial auto s/b Overhaul wheel loader 938f Alkon batching system Alkon batching system Mhw's computer control system - column B, the lookup value , say, 'Batching' I would want in column C, the lookup result that should return all incidence of the word Batching appearing in Column A i.e : -column C, result should show : Batching plant TT1 Eagle Tana Tran Batching system - MHw industrial 1 unit batching plant Alkon batching system Alkon batching system I have used this formula in C5, =INDEX($A$5:$A$36,(MATCH(FALSE,ISERROR(SEARCH("*"& B5&"*",$A$5:$A$36)), 0))) entered as array (Ctrl+Shift+Enter) and manage to return the first found result 'Batching plant TT1 Eagle Tana Tran' but were unable to find all the rest of the intended results, preferably listed in separate rows. Any suggestion or help on this is greatly appreciated. Thanks heaps ! Everyone. SauQ |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
Hi,
Try this ARRAY formula: =INDEX(D,LARGE(ROW(D)*ISNUMBER(SEARCH("*"&$B1&"*", D)),COLUMN(A1)),1) 1. Name the data in Column A - D for Data. 2. Drag the formula to the right as many columns as you have rows of data. This will cause some of the formulas to return #NUM! errors. 3. Apply the following conditional formatting to the formula range: Formula is =ISERR(C1) Click Format and set the Font color to white. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "SauQ" wrote: Dear all, I have in : - column A, a list of items/jobs : Batching plant TT1 Eagle Tana Tran 1 unit MSO4500 Batching system - MHw industrial Dismantling plant - p30/09/03 stanwell - spo tr122919 Dismantling plant - p30/09/03 stanwell - inv no : 03550 1 unit Gen set S25 1 unit batching plant 1 unit Tanaka indicator 1 unit plant fabrication Add mhw industrial automation 1 turbo gp (or-6904) for wheel loader Safety platform, water meter stand, piping, crusher run Add sd mhw industrial auto s/b Overhaul wheel loader 938f Alkon batching system Alkon batching system Mhw's computer control system - column B, the lookup value , say, 'Batching' I would want in column C, the lookup result that should return all incidence of the word Batching appearing in Column A i.e : -column C, result should show : Batching plant TT1 Eagle Tana Tran Batching system - MHw industrial 1 unit batching plant Alkon batching system Alkon batching system I have used this formula in C5, =INDEX($A$5:$A$36,(MATCH(FALSE,ISERROR(SEARCH("*"& B5&"*",$A$5:$A$36)), 0))) entered as array (Ctrl+Shift+Enter) and manage to return the first found result 'Batching plant TT1 Eagle Tana Tran' but were unable to find all the rest of the intended results, preferably listed in separate rows. Any suggestion or help on this is greatly appreciated. Thanks heaps ! Everyone. SauQ |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
Hi,
I realize I could shorten the previous formula to =INDEX(D,LARGE(ROW(D)*ISNUMBER(SEARCH($B1,D)),COLU MN(A1)),1) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "SauQ" wrote: Dear all, I have in : - column A, a list of items/jobs : Batching plant TT1 Eagle Tana Tran 1 unit MSO4500 Batching system - MHw industrial Dismantling plant - p30/09/03 stanwell - spo tr122919 Dismantling plant - p30/09/03 stanwell - inv no : 03550 1 unit Gen set S25 1 unit batching plant 1 unit Tanaka indicator 1 unit plant fabrication Add mhw industrial automation 1 turbo gp (or-6904) for wheel loader Safety platform, water meter stand, piping, crusher run Add sd mhw industrial auto s/b Overhaul wheel loader 938f Alkon batching system Alkon batching system Mhw's computer control system - column B, the lookup value , say, 'Batching' I would want in column C, the lookup result that should return all incidence of the word Batching appearing in Column A i.e : -column C, result should show : Batching plant TT1 Eagle Tana Tran Batching system - MHw industrial 1 unit batching plant Alkon batching system Alkon batching system I have used this formula in C5, =INDEX($A$5:$A$36,(MATCH(FALSE,ISERROR(SEARCH("*"& B5&"*",$A$5:$A$36)), 0))) entered as array (Ctrl+Shift+Enter) and manage to return the first found result 'Batching plant TT1 Eagle Tana Tran' but were unable to find all the rest of the intended results, preferably listed in separate rows. Any suggestion or help on this is greatly appreciated. Thanks heaps ! Everyone. SauQ |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Index match to return all values
Hello,
My UDF Pstat is still being developed further but you can already use it this way: Select a range of 4 rows and 2 columns and array-enter: =Pstat("sum",1,1-ISERROR(SEARCH(B1,A1:A17)),A1:A17,1-ISERROR(SEARCH (B1,A1:A17))) Pstat you can find he http://www.sulprobil.com/html/pstat.html Regards, Bernd |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return value with 2 conditions (using Lookup/Match/Index) | Excel Discussion (Misc queries) | |||
| Index? Match? Function to sort and return value fr diff column in | Excel Worksheet Functions | |||
| return multiple corresponding values using INDEX | Excel Worksheet Functions | |||
| Match/Index return #N/A | Excel Worksheet Functions | |||
| Error Return Value from and INDEX(A:2,MATCH()) function | Excel Worksheet Functions | |||
Hybrid Mode
