Searching for partial data in a column

[Guest]

I am searching for partial data in column and want to return all possible
queries.

Example: Search item in cell B1
Find all items on a second sheet in column a1 that contain b1 from original
sheet.
Not looking for exact matches, in other words a1 contains "exchangers" and
on the other sheet column a1 might contain "heat exchangers" or just
"exchangers"

I want to return not only "heat exchangers" or "exchangers" but also the
info in the rest of the row.

A1 B1
1 Item Location
2 Exchanger Tank Farm 1
3 Heat Exchanger Tank Farm 2

Thanks for any help.
If not clear ask and I will try and clearify.

 

[Domenic]

Assumptions:

On Sheet2...

A1:B10 contains your source data

First row contains your headers/labels

Formulas:

On Sheet1...

A1: enter your search term, such as 'Exchanger'

B1: enter 0 (zero)

B2, copied down:

=IF((Sheet2!A2<>"")*(ISNUMBER(SEARCH($A$1,Sheet2!A2))),LOOKUP(9.999999999
9999E+307,$B$1:B1)+1,"")

C1:

=LOOKUP(9.9999999999999E+307,B:B)

D2, copied down:

=IF(ROWS(D$22)<=$C$1,MATCH(ROWS(D$22),$B$2:$B$10,0),"")

E2, copied down and over to the Column F:

=IF(N($D2),INDEX(Sheet2!A$2:A$10,$D2),"")

Hope this helps!

 

[Dave Peterson]

Have you thought about using Data|Filter|Autofilter.

Then you can use the arrow in column A's header to choose
Custom|Contains
exchanger

If you need the data in another spot, you could copy and paste from there.