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