Referncing Rows of Filtered Data

[Mark T.]

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

 

[Mike H]

Mark,

I think you would be better telling us what you are trying to do.

Are these rows visible or hidden?
To work with visible rows in a filtered range have a look at 'Subtotal' in
help.

Mike

 

[T. Valko]

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(D215,SMALL(IF((SUBTOTAL(3,OFFSET(D215,ROW(D215)-MIN(ROW(D215)),,1)))*ROW(D215),(SUBTOTAL(3,OFFSET(D215,ROW(D215)-MIN(ROW(D215)),,1)))*ROW(D215)),3)-MIN(ROW(D215))+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)

 

[JMB]

I believe you can shorten that to:

=INDEX(D215,SMALL(IF(SUBTOTAL(3,OFFSET(D215,ROW(D215)-MIN(ROW(D215)),,1)),ROW(D215)),3)-MIN(ROW(D215))+1)

 

[T. Valko]

I believe you can shorten that

Yep, it cleans up nicely!

 

[Mark T.]

Thanks so much. I'll be putting this in place today!!
-Mark

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thanks so much. I'll be putting this in place today!!
-Mark

 

[Lori]

Or even:

=INDEX(D215,MATCH(3,SUBTOTAL(3,OFFSET(D215,,,ROW(D215)-MIN(ROW(D215))+1)),0))

 

[JMB]

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

Or even:

=INDEX(D215,MATCH(3,SUBTOTAL(3,OFFSET(D215,,,ROW(D215)-MIN(ROW(D215))+1)),0))