Referncing Rows of Filtered Data

M

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
 
M

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

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(D2:D15,SMALL(IF((SUBTOTAL(3,OFFSET(D2:D15,ROW(D2:D15)-MIN(ROW(D2:D15)),,1)))*ROW(D2:D15),(SUBTOTAL(3,OFFSET(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)
 
J

JMB

I believe you can shorten that to:

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

Lori

Or even:

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top