how to use partial (word) search

[Vikky]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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]

Hi Bob;

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

Regards
Vikky