Using OFFSET to select a value from filtered list

I have a sample file for better clarification.

In my file, you will see I have products and the category they reside in. I have filters on the data and one is applied so that only Benchtop Ionizers appear from my Tertiary Category column. What I want to happen, is cell D1 to display the name of whatever category is filtered upon. I tried the Offset but that will only show cell C6. When I filter to another category D1 will remain displayed as Benchtop Ionizers. Try for yourself. I saw that people are using Indexs and Matches, coupled with the Subtotal and Offset functions, but I cannot replicate it properly. I have the SUBTOTAL formula working okay by itself where it counts only the number of skus based on the filter. This is in cell B1. Can anyone help me out here?

This might be what you're looking for.

In cell D1

Formula:

Please Login or Register  to view this content.

Ahh quekbc, you are a life saver, that was it. I was missing the lookup value in the match with that formula. Adding that did the trick. Thanks a ton for your help!

when you have a moment, can you explain why the height needs to be 1? I am not understanding that part of the formula.

=OFFSET(reference, rows, columns, [height], [width]) for convenience
That's because the reference range within the OFFSET function is referring to cells with a height of 16 (row 6 to 21). Doing an OFFSET without re-specifying the height will maintain it at a height of 16 cells.

Setting the height to 1 ensures that you're looking at individual cells, and thus the COUNTA function will only return 1s for visible cells or 0 for non-visible cells. (If height is not set to 1, hidden cells may be counting visible cells as it references 16 cells below it).

For example,
=OFFSET(A1:A3,0,1) gives you B1:B3.
=OFFSET(A1:A3,0,1,1) gives you B1