Small if Array K- Use with filter

I have a formula

Please Login or Register  to view this content.

I am trying to get the next match on filtered rows using the subtotal function for the K part. The problem is that when I unfilter, it returns the incorrect result as the K has changed because it is adding all the rows. Is there a way to change the K part so the result will remain the same for filtered and unfiltered rows and making sure it also increments by 1 as well?

I will be using Excel 2007.

I am kind of confused as to what you are trying to accomplish.

Firstly, what is K part?

Secondly, what exactly are you trying to count and on what conditions?

Please show expected results based on filtering you perform.

Perhaps try this version

=INDEX($B$9:$B$11,SMALL(IF($A$9:$A$11<>"",ROW($A$9:$A$11)-ROW($A$9)+1),COUNT(C$1:C1)+1))

So, I am tyring to get all the matches for A that is in the lookup table A9 to A12.
I am using the K part for the small function to find the next match in my lookup.
Most people use Rows($A$1:A1) to increment by 1, or manually put in the number 2,3 and so on.
I am using subtotal to use with hidden rows, but it is not working properly.

The problem is that it does not increase by one if the rows that are being filled in with the lookup are filtered and not contiguous.

Daddylonglegs that works, but will not work properly if there are numbers in the hidden rows. Is there a way to put maybe the references for the Count or maybe even numbers like 1,2,3... outside the filtered range to reference and have it still increment by one?