Combining INDEX and OFFSET functions on a filtered list

shirleyj

New Member
Joined
Sep 14, 2011
Messages
37
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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0
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
 
Upvote 0
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! :cool:

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))
 
Upvote 0
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
 
Upvote 0
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! :cool:
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,209
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top