how to use partial (word) search

Hi;

Can any one help me in putting a formula for partial search (word) in a
coulam or row and result would be the cell value containts that
searched word or value.

Sample Data :

Search : Sathyam

Data:

Sanraj Computers
Santosh Computers
Santosh Enterprises Pvt Ltd
Sap Informatics
Sarthak Systems
Sas Technologies
Sastek Infosys India Pvt Ltd
Satellite Computer Services
Sathyam Computers
Sathyam Computers & Consultancy
Satyasai Information Technology Pvt Ltd
Savithri Digital Computers
Scl India Pvt Ltd
Scott Systems
Sd Info System
Sds Enterprises
Seetaram Computers

Result : Sathyam Computers
Sathyam Computers & Consultancy


Thank you.

Regards

Vikky

Select a bunch of cells, then in the formula bar enter

=IF(ISERROR(SMALL(IF(ISNUMBER(FIND("Sathyam",A1:A20)),ROW($A1:$A20),""),ROW(
$A1:$A20))),"",
INDEX($A$1:$A$20,SMALL(IF(ISNUMBER(FIND("Sathyam",A1:A20)),ROW($A1:$A20),"")
,ROW($A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Vikky" <love.excel@gmail.com> wrote in message
news:1152874105.815450.98830@35g2000cwc.googlegroups.com...
> Hi;
>
> Can any one help me in putting a formula for partial search (word) in a
> coulam or row and result would be the cell value containts that
> searched word or value.
>
> Sample Data :
>
> Search : Sathyam
>
> Data:
>
> Sanraj Computers
> Santosh Computers
> Santosh Enterprises Pvt Ltd
> Sap Informatics
> Sarthak Systems
> Sas Technologies
> Sastek Infosys India Pvt Ltd
> Satellite Computer Services
> Sathyam Computers
> Sathyam Computers & Consultancy
> Satyasai Information Technology Pvt Ltd
> Savithri Digital Computers
> Scl India Pvt Ltd
> Scott Systems
> Sd Info System
> Sds Enterprises
> Seetaram Computers
>
> Result : Sathyam Computers
> Sathyam Computers & Consultancy
>
>
> Thank you.
>
> Regards
>
> Vikky
>

or put the lookup value in a cell, C1 say, and use

=IF(ISERROR(SMALL(IF(ISNUMBER(FIND(C1,A1:A20)),ROW($A1:$A20),""),ROW($A1:$A2
0))),"",
INDEX($A$1:$A$20,SMALL(IF(ISNUMBER(FIND(C1,A1:A20)),ROW($A1:$A20),""),ROW($A
1:$A20))))

in the same way

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Vikky" <love.excel@gmail.com> wrote in message
news:1152874105.815450.98830@35g2000cwc.googlegroups.com...
> Hi;
>
> Can any one help me in putting a formula for partial search (word) in a
> coulam or row and result would be the cell value containts that
> searched word or value.
>
> Sample Data :
>
> Search : Sathyam
>
> Data:
>
> Sanraj Computers
> Santosh Computers
> Santosh Enterprises Pvt Ltd
> Sap Informatics
> Sarthak Systems
> Sas Technologies
> Sastek Infosys India Pvt Ltd
> Satellite Computer Services
> Sathyam Computers
> Sathyam Computers & Consultancy
> Satyasai Information Technology Pvt Ltd
> Savithri Digital Computers
> Scl India Pvt Ltd
> Scott Systems
> Sd Info System
> Sds Enterprises
> Seetaram Computers
>
> Result : Sathyam Computers
> Sathyam Computers & Consultancy
>
>
> Thank you.
>
> Regards
>
> Vikky
>

Hi Bob;

Thanku Very much for your help i have got the desired Result.

Regards
Vikky



Bob Phillips wrote:
> or put the lookup value in a cell, C1 say, and use
>
> =IF(ISERROR(SMALL(IF(ISNUMBER(FIND(C1,A1:A20)),ROW($A1:$A20),""),ROW($A1:$A2
> 0))),"",
> INDEX($A$1:$A$20,SMALL(IF(ISNUMBER(FIND(C1,A1:A20)),ROW($A1:$A20),""),ROW($A
> 1:$A20))))
>
> in the same way
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Vikky" <love.excel@gmail.com> wrote in message
> news:1152874105.815450.98830@35g2000cwc.googlegroups.com...
> > Hi;
> >
> > Can any one help me in putting a formula for partial search (word) in a
> > coulam or row and result would be the cell value containts that
> > searched word or value.
> >
> > Sample Data :
> >
> > Search : Sathyam
> >
> > Data:
> >
> > Sanraj Computers
> > Santosh Computers
> > Santosh Enterprises Pvt Ltd
> > Sap Informatics
> > Sarthak Systems
> > Sas Technologies
> > Sastek Infosys India Pvt Ltd
> > Satellite Computer Services
> > Sathyam Computers
> > Sathyam Computers & Consultancy
> > Satyasai Information Technology Pvt Ltd
> > Savithri Digital Computers
> > Scl India Pvt Ltd
> > Scott Systems
> > Sd Info System
> > Sds Enterprises
> > Seetaram Computers
> >
> > Result : Sathyam Computers
> > Sathyam Computers & Consultancy
> >
> >
> > Thank you.
> >
> > Regards
> >
> > Vikky
> >