To add even more flexibilty and be able to find the criteria *anywhere
within* the string:

=IF(ROWS(D$1:D1)<=COUNTIF(A$1:A$7,"*"&$C$1&"*"),IN DEX(B$1:B$7,SMALL(IF(ISNUMBER(SEARCH($C$1,A$1:A$7) ),ROW(B$1:B$7)-ROW(B$1)+1),ROWS(D$1:D1))),"")

C1 = sh

This would match strings like:

Fish
Sheep
Trisha

Biff

"Domenic" wrote in message
...
Assuming that C1 contains the search value of interest, such as 'sh',
try the following formula instead...

=IF(ROWS(D$1:D1)<=COUNTIF(A$1:A$7,"*"&$C$1),INDEX( B$1:B$7,SMALL(IF(RIGHT(
A$1:A$7,LEN($C$1))=$C$1,ROW(B$1:B$7)-ROW(B$1)+1),ROWS(D$1:D1))),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article . com,
"bdh_google" wrote:

I guess I was hoping for the search value to be more flexible, in that
it may be referenced from its own cell instead of within the actual
formula. That way if I were to search for other
options/characters/etc., I would not have to change the formula code
and would only have to change the single cell.

Thoughts?
-bdh