Index match to return all values

S

SauQ

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
 
A

Ashish Mathur

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(SEARCH($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
 
R

Ron Rosenfeld

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
Click to expand...

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
 
T

T. Valko

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(ISNUMBER(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
 
S

SauQ

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
 
S

Shane Devenshire

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.
 
S

Shane Devenshire

Hi,

I realize I could shorten the previous formula to

=INDEX(D,LARGE(ROW(D)*ISNUMBER(SEARCH($B1,D)),COLUMN(A1)),1)
 
S

SauQ

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
 
S

SauQ

Yup, I array entered (Ctrl+Shift+Enter)

Still returns "", ie. blank.

Many thanks, Ashish.

SauQ
 
S

SauQ

Dear all

Just an after thought, would it be possible to convert the formula
into a UDF?

Thanks heaps! everyone
SauQ
 
R

Ron Rosenfeld

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
Click to expand...

You're welcome. Thanks for the feedback.
--ron
 
B

Bernd P

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 here:
http://www.sulprobil.com/html/pstat.html

Regards,
Bernd
 
Top