searching method required

[pradeepvarkala]

Hi all

I am really glad to be in this forum. i have joined in this group to share some doubts
i have one excel file "A" which contains lots of beneficiary details of a city(above 100000 lakh records)
there is no case of duplication but still some beneficiaries have same name (not duplicated)
i need to search a particular name(listed in another file"B") and identify all those beneficiaries in file "A"(not only the first occurrence) with that particular name or similar name and there serial number

Is there any method to solve the same.

I am uploading the draft copy of excel


expecting a help/suggestions from experts

 

[Naresh Yadav]

Dear Pradeep,

referred to the attached file..

 

[bosco_yip]

Another option in non CSE proposal,

I6, formula copy down :

=LOOKUP(ROWS(A$1:A1),COUNTIF(OFFSET(B$1,,,ROW($1:$15)),"*"&F$5&"*")+1,B$2:B$15)&""

Regards
Bosco

 

[Naresh Yadav]

Respected Bosco Sir,

Thanks for posting once again an Awesome formula here
and further i have a question in my mind that while evaluating the "COUNTIF(OFFSET($B$1,,,ROW($I$1:$I$15)),"*"&$F$5&"*")+1 part,initially we get "#VALUE", than how come suddenly its gets converted in to numerical value at end of the formula evaluation

 

[bosco_yip]

Respected Bosco Sir,

Thanks for posting once again an Awesome formula here
and further i have a question in my mind that while evaluating the "COUNTIF(OFFSET($B$1,,,ROW($I$1:$I$15)),"*"&$F$5&"*")+1 part,initially we get "#VALUE", than how come suddenly its gets converted in to numerical value at end of the formula evaluation

Highlight that area and press F9

=LOOKUP(ROWS(A$1:A1),COUNTIF(OFFSET(B$1,,,ROW($1:$15)),"*"&F$5&"*")+1,B$2:B$15)&""

become……

=LOOKUP(ROWS(A$1:A1),{1;2;2;2;2;3;3;3;3;3;3;3;3;4;4},B$2:B$15)&""

Regards
Bosco