Need a formula to lookup column header based on data results within a table

[freeb1893]

Below is an example data set:

Period</SPAN>	5302</SPAN>	1048</SPAN>	1028</SPAN>	1037-1</SPAN>	5107</SPAN>	1022</SPAN>	5101</SPAN>
Release Period 1</SPAN>	Good	Not Clear	Good	Good	Good	Not Clear	Good
Release Period 2</SPAN>	Good	Good	Good	Good	Good	Good	Good
Release Period 3</SPAN>	Good	Good	Good	Not Clear	Good	Good	Good

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL span=5></COLGROUP>

Basically, I want to create a formula where a result table will already have the row headers "Release Period 1", "Release Period 2", etc. and will return to the right each column header that has a "Not Clear" value within the correspodning matching row.

For example, for my formula in my results table to the right of "Release Period" 1, it would return in the adjacent cells to the right, all the column headers that have a "Not Clear" value within that period, in which case would be 1048, and the next cell would be 1022.

For Release Period 2, no column headers would be returned, since there are no Not Clear's in Release Period 2.

For Release Period 3, the adjacent cell would return 1037-1.

Any idea how to put this into a formula? Where it queues off the row header and searchs for "Not Clear's" to return the column header from the original data table? I assume I'll need it as an array formula.

 

[mumps]

Peter, thank you so much for your feedback.

 

[Robert Mika]

@Robert re Excel jeanie
- Might you consider turning on the jeanie gridlines to make your screen shots clearer (in my mind anyway)?
For example, with a quick glance at the "1022" value in the top row of your screen shot it isn't immediately clear whether that value is in G1 or H1. Gridlines would remove any doubt.
- Are you aware of what is causing the asterisks in your screen shot?

Thanks Peter.
1) Will do.
2) Not sure -I'm using Excel Jeanie on 2007 version.
You have mentioned somwhere that the editing could couse the issue and as far as I know that could be the problem.
I would keep eye on this more closely.

 

[Peter_SSs]

2) Not sure -I'm using Excel Jeanie on 2007 version.
You have mentioned somwhere that the editing could couse the issue and as far as I know that could be the problem.

Yes, editing or previewing your post causes the asterisks. If I do either then I re-paste the jeanie code over the old code before final submission. It is usually still on the clipboard so easy to do, but if not I re-generate it from my worksheet, usually only takes a few seconds.