Extract multiple row headers and column headers if criteria is met in multiple columns

I am trying to extract, failing of course, the row header and the column header for each unit that has been closed. So, I am looking for a formula to extract all IDs or days based on the criteria. I've attached a file to explain better.

One way, in B2, copied down:

=INDEX(Sheet1!$B$1:$H$1,MATCH("Closed",INDEX(Sheet1!$B$2:$H$9,MATCH(Sheet2!A2,Sheet1!$A$2:$A$9,0),),0))

That would be easy Glenn, but unfortunately, I don't know the IDs or the days. Basically I need to extract all IDs that are closed as well as all days similar to a "extract unique formula"

OK... That wasn't clear to me.

In A2:
=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$B$2:$H$9)/(Sheet1!$B$2:$H$9="Closed"),ROWS(A$2:A2))),"")

In B2:
=IF(A2="","",INDEX(Sheet1!$B$1:$H$1,MATCH("Closed",INDEX(Sheet1!$B$2:$H$9,MATCH(Sheet2!A2,Sheet1!$A$2:$A$9,0),),0)))

Thank you Glenn, I've done some testing and these 2 formulas work great as always. But I encountered an issue. This list gets updated on a week basis from a cube. Therefore, out of 7 days, sometimes we have data unfeed(blanks). How can I include a countif(range, >0) to exclude the returns that do not contain values, and to return only closed without blanks.

You've lost me. Can you post a sample sheet illustrating that issue? (I see problems much more easily better than I read about them!!)

I've attached a file with the exact layout as in my actual file and with what I am trying to achieve.

Ummm. That looks JUST like the original.. So, I don't yet understand what you're after regarding blank cells.... Some rows contain closed and blank cells, but are still included in your expected results??? Am I missing the point here?

I know is looks fine and I kinda sound crazy. Ok, let me explain from beginning. So the formulas that i have in my actual file have some formulas looking like this:

Formula:

Please Login or Register  to view this content.

Obviously there are a lot more complex but I removed the sensitive data.
Each formula will return a blank, but is cube based. I thought that I may be wrong about this and did some testing using =char(code(one supposedly blank cell)) and looks like it returned a C(code 67).
I fail to understand how.

Apologies Glenn, fixed the problem. It lies within the underline formulas. The cell is actually returned as closed, but custom formatted. Your formula fixed my issues, I just need to change other formulas. Many thanks for your time and help!

RoFLAO. Reaches for the whiskey bottle....

Are you saying that in the file attached at Post 5 (are you 100% sure this is the correct attachment???) I should only return data relating to IDs 6 & 7 because they are closed AND there are no blanks in the range??? Or do I pour myself a large one??

Just saw Post 10. I might put the bottle back on the shelf again...