Partially match 2nd or 3rd occurance

[fari1]

I've a formula which matches 2nd or 3rd and so on occurances of a text string in column A of sheet1, here's the formula

=SMALL(IF(TRIM(sheet2!A1:A1500)=TRIM($F$1),ROW(sheet2!A1:A1500)),4)

this formula works great,when it finds the exact match,but it fails,when text string has something added to it, e.g my text string in F1 is united bank limited, this formula will be great when exact match is found,but it is united bank ltd., it wont give me the results,similarly like the united bank ltd etc.
i want this formula to work flexibly for me to match partially the text string.
partiall match formula is

=MATCH("*"&E1&"*",$A$1:$A$1000,0)

and i'm unable to merge them to make them use for me

 

[Ron Coderre]

It's unlikely that a formula can be created that will foresee every permutation of a name that a human might enter.

Examples for "Internal Revenue Service"
IRS
I.R.S.
I R S
Intl Rev Svc
Internal Rev Svc.
etc

I would concentrate on standardizing entered data using data validation or any other means to ensure that the same value is ALWAYS entered for the same item.

 

[fari1]

i've suggestion why not use left formula for the text string to find and also in the range where this string has to be found, is that possible