Return Column header after matching cell value

[aton911]

Good Afternoon,

I hope you can help as I am trying to return column header after matching cell value. For example when search for "Cat" it should return Col 3

[TABLE="width: 448"]


<colgroup><col width="64" style="width: 48pt;" span="7">
<tbody>[TR]

[TD="width: 64, bgcolor: transparent"]col1[/TD]

[TD="width: 64, bgcolor: transparent"]col2[/TD]

[TD="width: 64, bgcolor: transparent"]col3[/TD]

[TD="width: 64, bgcolor: transparent"]col4[/TD]

[TD="width: 64, bgcolor: transparent"]col5[/TD]

[TD="width: 64, bgcolor: transparent"]col6[/TD]

[TD="width: 64, bgcolor: transparent"]col7[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"]Dog[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"]Dog[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"]Dog[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"]Dog[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"]Dog[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"]Dog[/TD]

[TD="bgcolor: transparent"]Test[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"]Dog[/TD]

[TD="bgcolor: transparent"]Test[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"]Dog[/TD]

[TD="bgcolor: transparent"]Test[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"][/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"]Dog[/TD]

[TD="bgcolor: transparent"]Test[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"]Horse[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"]Dog[/TD]

[TD="bgcolor: transparent"]Test[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"]Horse[/TD]

[/TR]

[TR]

[TD="bgcolor: transparent"]Animal[/TD]

[TD="bgcolor: transparent"]Apple[/TD]

[TD="bgcolor: transparent"]Cat[/TD]

[TD="bgcolor: transparent"]Dog[/TD]

[TD="bgcolor: transparent"]Test[/TD]

[TD="bgcolor: transparent"][/TD]

[TD="bgcolor: transparent"]Horse[/TD]

[/TR]


</tbody>[/TABLE]

 

[aton911]

Please can any one suggest a formula ?

 

[palaeontology]

try this ... use ctrl shift enter ...

=INDEX(A1:G1,MAX(IF(A2:G17=D21,COLUMN(A2:G17)-COLUMN(A1)+1)))

 

[kalikj]

Aton, or if you had headings in the first row / row 1 rather than col1, col 2 ...etc, and assuming your table started in cell A1, and that you wanted to enter your search in the relevant cell in column 8 (or column H), then enter the following formula in cell I2 (i.e. to search the first row of your table assuming it was laid out as I have specified and return the column heading corresponding to the search term entered in H2):

=INDEX($A$1:$G$1,1,MATCH(H2,$A2:$G2,0))

Please let me know if this is unclear. It is important to specify exactly how the table is laid out in order for me to give you the exact formula.

You can then copy this formula down from I2 to the last row in column I in your table. This will give you the column heading corresponding to where your search word appears in each row of the table.