This array formula** will return the 1st item from the range D2:D15 when a
filter is applied.

=INDEX(D2:D15,MATCH(1,SUBTOTAL(3,OFFSET(D2:D15,,,R OW(D2:D15)-ROW(D2)+1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Aashish" wrote in message
...
how to assign cell value which the users has filtered by?