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

freeb1893 · Mar 29, 2013

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

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.

Robert Mika · Mar 29, 2013

freeb1893 said:
Below is an example data set:

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

<tbody>
</tbody>

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.

Sheet1

*	A	B	C	D	E	F	G	H
1	Period	5302	1048	1028	1037-1	5107	1022	5101
2	Release Period 1	Good	Not Clear	Good	Good	Good	Not Clear	Good
3	Release Period 2	Good	Good	Good	Good	Good	Good	Good
4	Release Period 3	Good	Good	Good	Not Clear	Good	Good	Good
5	*	*	*	*	*	*	*	*
6	*	*	*	*	*	*	*	*
7	*	*	*	*	*	*	*	*
8	Release Period 3	1037-1	*	*	*	*	*	*

Spreadsheet Formulas

Cell	Formula
B8	{=IFERROR(INDEX($B$1:$H$1,SMALL(IF(INDEX($B$2:$H$4,MATCH($A$8,$A$2:$A$4,0),0)="not clear",COLUMN($B$2:$H$4)-ROW($B$2)+1),COLUMNS($A$2:A2))),"")}

Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

Drag accross.

mumps · Mar 29, 2013

VBA approach: The results will appear in Sheet2.

Code:

Sub Test()
    Dim bottomA As Integer
    bottomA = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Sheets("Sheet1").Range("A1:A" & bottomA).Copy Sheets("Sheet2").Range("A1")
    For Each c In Sheets("Sheet1").Range("B2:H" & bottomA)
        If c = "Not Clear" Then
            Cells(1, c.Column).Copy Sheets("Sheet2").Range("IV" & c.Row).End(xlToLeft).Offset(0, 1)
        End If
    Next c
End Sub

freeb1893 · Mar 29, 2013

It appears something isn't clicking with this formula. It's not causing an error, but it's not providing the column header in return, it's populating with a blank space.

Robert Mika said:
Sheet1

*
A
B
C
D
E
F
G
H

1
Period
5302
1048
1028
1037-1
5107
1022
5101

2
Release Period 1
Good
Not Clear
Good
Good
Good
Not Clear
Good

3
Release Period 2
Good
Good
Good
Good
Good
Good
Good

4
Release Period 3
Good
Good
Good
Not Clear
Good
Good
Good

5
*
*
*
*
*
*
*
*

6
*
*
*
*
*
*
*
*

7
*
*
*
*
*
*
*
*

8
Release Period 3
1037-1
*
*
*
*
*
*

<TBODY>
</TBODY>

Spreadsheet Formulas

Cell
Formula

B8
{=IFERROR(INDEX($B$1:$H$1,SMALL(IF(INDEX($B$2:$H$4,MATCH($A$8,$A$2:$A$4,0),0)="not clear",COLUMN($B$2:$H$4)-ROW($B$2)+1),COLUMNS($A$2:A2))),"")}

<TBODY>
</TBODY>

Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!

<TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

Drag accross.

Robert Mika · Mar 29, 2013

Did you confirm Control-Shift-Enter?

Peter_SSs · Mar 29, 2013

Robert Mika said:
=IFERROR(INDEX($B$1:$H$1,SMALL(IF(INDEX($B$2:$H$4,MATCH($A$8,$A$2:$A$4,0),0)="not clear",COLUMN($B$2:$H$4)-ROW($B$2)+1),COLUMNS($A$2:A2))),"")

I think Robert's formula should work for you, provided ..
1. You adjust for your actual layout (test first with the exact layout shown)
2. Remove the red $ sign shown above.
3. As indicated earlier in the thread, enter the formula without the {} but confirm it with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and down.

freeb1893 · Apr 1, 2013

Thanks! Sorry about the dupe. It's is indexing now, but it's not indexing the right data for some reason. It appears to be indexing the column header 2 columns to the right of every "Not Clear" hit, rather than the column header directly above the "Not Clear" hit. Any ideas?

Peter_SSs said:
I think Robert's formula should work for you, provided ..
1. You adjust for your actual layout (test first with the exact layout shown)
2. Remove the red $ sign shown above.
3. As indicated earlier in the thread, enter the formula without the {} but confirm it with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across and down.

Robert Mika · Apr 1, 2013

freeb1893 said:
Thanks! Sorry about the dupe. It's is indexing now, but it's not indexing the right data for some reason. It appears to be indexing the column header 2 columns to the right of every "Not Clear" hit, rather than the column header directly above the "Not Clear" hit. Any ideas?

I think is time for this:
https://dl.dropbox.com/u/78583609/headings.xlsx

mumps · Apr 1, 2013

Hello to all. I was just wondering if anyone has tried the macro I suggested in Post# 3.

Peter_SSs · Apr 2, 2013

freeb1893 said:
Thanks! Sorry about the dupe. It's is indexing now, but it's not indexing the right data for some reason. It appears to be indexing the column header 2 columns to the right of every "Not Clear" hit, rather than the column header directly above the "Not Clear" hit. Any ideas?

That would indicate that your layout is different to Robert's. Your sample data gave no indication of what rows and/or columns it occupies so Robert has taken what appears to me to be a pretty good stab at interpreting your image.

Assuming yours is different, for a start I suggest you make a sheet exactly like Robert's to prove that his formu works, then see if you can adapt to your layout. If not, post back with more details about that.

mumps said:
Hello to all. I was just wondering if anyone has tried the macro I suggested in Post# 3.

I hadn't because the OP specifically asked for a formula approach.
Since you seem to want some feedback, I have now looked and it certainly does what you say. I guess some things that may need consideration are ..
- Does Sheet2 already exist?
- If so, does it already contain data that might be over-written by the macro?
- Does the OP want the results to automatically update if data in the original table is altered, as would happen with the formula approach?

@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?

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

freeb1893

Board Regular

Excel Facts

Robert Mika

MrExcel MVP

mumps

Well-known Member

freeb1893

Board Regular

Robert Mika

MrExcel MVP

Peter_SSs

MrExcel MVP, Moderator

freeb1893

Board Regular

Robert Mika

MrExcel MVP

mumps

Well-known Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

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

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

Board Regular

Excel Facts

MrExcel MVP

Well-known Member

Board Regular

MrExcel MVP

MrExcel MVP, Moderator

Board Regular

MrExcel MVP

Well-known Member

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock