To return the values in Column B where the last two characters for the
corresponding values in Column A is 'sh', try...

B10, copied down:

=IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$7,"*sh"),INDEX (B$1:B$7,SMALL(IF(RIGHT
($A$1:$A$7,2)=$A$10,ROW($A$1:$A$7)-ROW($A$1)+1),ROWS(B$10:B10))),"")

....confirmed with CONTROL+SHIFT+ENTER.

To return the values in Column B where the corresponding values in
Column A contain 'sh' anywhere in the string, try the following formula
instead...

B10, copied down:

=IF(ROWS(B$10:B10)<=COUNTIF($A$1:$A$7,"*sh*"),INDE X(B$1:B$7,SMALL(IF(ISNU
MBER(SEARCH($A$10,$A$1:$A$7)),ROW($A$1:$A$7)-ROW($A$1)+1),ROWS(B$10:B10))
),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
xlarch wrote:

I don't quite understand what you mean by criteria for a1:a10. I'm pretty new
to excel.
Just to put my question in perspective...
this is an example of what i'm doing:
http://office.microsoft.com/en-us/as...#Return%20one%
20corresponding
As you will see at the bottom of the above page/link i put the value
"ashish"in a10, and it returns corresponding numbers.
if i replace "ashish" with "sh"in a10, i need to get all the corresponding
values from column b, for every cell in column a that has "sh", which would
mean it gives me the following values:

a b
10 sh 234
11 534
12 634
13 734
14 834
since(sh is a part of a1,a4,a5,a6,a7)
Hope this clarifies it a little better!
Do let me know if you figure it out!
Thanks for your help!