Combining INDEX and OFFSET functions on a filtered list

[shirleyj]

Hi Mr Excel,

I am really wracking my brains on a difficult Excel issue here, but despite much searching of the forums, I cannot quite find the solution I am looking for.

I am using the INDEX function to return the value from a database. So far, so easy. However, the problems come when the list is filtered.

The basic syntax of my formula is as follows:

=INDEX(complete_db, counter, 2)

The dynamic range named ‘counter’ indicates the row position. However, when the list is filtered, if the ‘counter’ is 3, it will return row 3, which could be hidden by the filter. I want the formula to return position 3 in the filtered list - which could could be row 359, for example.

The closest solution I found was on excelbanter.com, which makes use of an array formula: http://www.excelbanter.com/showthread.php?t=178211
While this partially works , I am not sure how to tweak the formula to indicate how many rows to offset from the header row on the filtered list, using the dynamic ‘counter’. This array formula only shows what is directly under the header row.

I hope the problem is not terribly complicated – and somebody out there could point me in the right direction.

Any help would be really appreciated!

Best regards,

Shirley

 

[T. Valko]

Hi Mr Excel,

I am really wracking my brains on a difficult Excel issue here, but despite much searching of the forums, I cannot quite find the solution I am looking for.

I am using the INDEX function to return the value from a database. So far, so easy. However, the problems come when the list is filtered.

The basic syntax of my formula is as follows:

=INDEX(complete_db, counter, 2)

The dynamic range named ‘counter’ indicates the row position. However, when the list is filtered, if the ‘counter’ is 3, it will return row 3, which could be hidden by the filter. I want the formula to return position 3 in the filtered list - which could could be row 359, for example.

The closest solution I found was on excelbanter.com, which makes use of an array formula: http://www.excelbanter.com/showthread.php?t=178211
While this partially works , I am not sure how to tweak the formula to indicate how many rows to offset from the header row on the filtered list, using the dynamic ‘counter’. This array formula only shows what is directly under the header row.

I hope the problem is not terribly complicated – and somebody out there could point me in the right direction.

Any help would be really appreciated!

Best regards,

Shirley

It'll be a lot easier if you use range addresses rather than named ranges.

Let's assume the range of interest is D2:D15.

To extract the 3rd visible cell value from D2:D15...

Array entered**:

=INDEX(D2:D15,MATCH(3,SUBTOTAL(3,OFFSET(D2:D15,,,ROW(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.

 

[shirleyj]

Hi Biff,

Wow, I am so glad I sent this question to the forum! You have solved my problem in a matter of minutes, what has baffling me for hours!! Thank you so much for your help. It works perfectly.

Have a great rest of day - and thanks again!

Shirley

 

[T. Valko]

Hi Biff,

Wow, I am so glad I sent this question to the forum! You have solved my problem in a matter of minutes, what has baffling me for hours!! Thank you so much for your help. It works perfectly.

Have a great rest of day - and thanks again!

Shirley

You're welcome. Thanks for the feedback!

Just to expand on this for future reference.

To extract the nth item just change N as needed:

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

 

[shirleyj]

Hi Biff,

Yes, I figured 'N' could be substituted for a dynamically named range, which is exactly what I need for my workbook to function. I could have never come up with this formula myself! Thanks very much for your expertise.

Have a great day.

Shirley

 

[T. Valko]

Hi Biff,

Yes, I figured 'N' could be substituted for a dynamically named range, which is exactly what I need for my workbook to function. I could have never come up with this formula myself! Thanks very much for your expertise.

Have a great day.

Shirley

You're quite welcome!