This array formula** will return the 1st item from the range D2
15 when a
filter is applied.
=INDEX(D2
15,MATCH(1,SUBTOTAL(3,OFFSET(D2
15,,,ROW(D2
15)-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.