Thanks for your help.
What if "sh" is a variable that i want to keep changing, i.e a10 is a
variable, so that when i put different values in a10, i get different sets of
data?
Can either of the formulae (the two you sent me and the original one) be
made to work in that way?
thanks again Domenic


"Domenic" wrote:

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!