Hi Domenic,

Thank you very much. The Formula works Great!

=INDEX(Store,MATCH(2,1/SUBTOTAL(3,OFFSET(Store,ROW(Store)-MIN(ROW(Store))
,0,1))),ROWS($A$2:A2))

...confirmed with CONTROL+SHIFT+ENTER.

Cheers,
Sam

Domenic wrote:
Assuming that you'd like to return the relevant values in Column A,
starting at A2, try...

A2, copied down:

=INDEX(Store,LOOKUP(2,1/SUBTOTAL(3,OFFSET(Store,ROW(Store)-MIN(ROW(Store)
),0,1)),ROW(Store)-MIN(ROW(Store))+1),ROWS($A$2:A2))

...confirmed with just ENTER, or

=INDEX(Store,MATCH(2,1/SUBTOTAL(3,OFFSET(Store,ROW(Store)-MIN(ROW(Store))
,0,1))),ROWS($A$2:A2))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

Hi All,

[quoted text clipped - 4 lines]
Cheers,
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200605/1