Very nice - I'll have to try to remember that.

"Lori" wrote:

Or even:

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

"JMB" wrote:

I believe you can shorten that to:

=INDEX(D2:D15,SMALL(IF(SUBTOTAL(3,OFFSET(D2:D15,RO W(D2:D15)-MIN(ROW(D2:D15)),,1)),ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1)


"T. Valko" wrote:

Assume the unfiltered range is A2:F15.

The 4th column would be column D so this formula will return what's on the 3
visible row from column D (assuming there are no empty cells within the
filtered range of column D).

Array entered** :

=INDEX(D2:D15,SMALL(IF((SUBTOTAL(3,OFFSET(D2:D15,R OW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15),(SUBTOTAL(3,OFF SET(D2:D15,ROW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15)),3)-MIN(ROW(D2:D15))+1)

If there aren't 3 visible rows then you'll get an error.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Mark T." wrote in message
...
I need an Index() function that works against filtered data. I have
filtered
data and I want to be able to reference row 3, column 4 of the filtered
data
only using the Index() funtion. The regular Index () function goes against
all data, even rows hiden by the filter. Can any provide a solution?

Thanks,
-Mark