Great, that works, but it seems that I can't do any calculations with the
result. It looks like Excel doesn't recognize the formula result as a
reference. Is there any way to get around with this?
I want to use the results (reference) in a formula ('bootmean' from Poptools
addin)which may only use cells in column B from a certain class (defined in
column A).

Is there a way to make Excel recognize the result of the formula below as a
reference?

Many thanks,
Maarten

"pinmaster" wrote:


Hi, try this:

="B"&ROW(INDEX(A:A,MATCH("a",A:A,0)))&":"&"B"&ROW( INDEX(A:A,MATCH("a",A:A,0)))+COUNTIF(A:A,"a")-1

or

="B"&ROW(INDEX(A:A,MATCH(F1,A:A,0)))&":"&"B"&ROW(I NDEX(A:A,MATCH(F1,A:A,0)))+COUNTIF(A:A,F1)-1

where F1 is your lookup value.

HTH
JG


--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261
View this thread: http://www.excelforum.com/showthread...hreadid=494109