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

col1	col2	col3	col4	col5	col6	col7
Animal	Apple
Animal	Apple
Animal	Apple
Animal	Apple	Cat
Animal	Apple	Cat
Animal	Apple	Cat	Dog
Animal	Apple	Cat	Dog
Animal	Apple	Cat	Dog
Animal	Apple	Cat	Dog
Animal	Apple	Cat	Dog
Animal	Apple	Cat	Dog	Test
Animal	Apple	Cat	Dog	Test
Animal	Apple	Cat	Dog	Test
Animal	Apple	Cat	Dog	Test		Horse
Animal	Apple	Cat	Dog	Test		Horse
Animal	Apple	Cat	Dog	Test		Horse

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

























































































































































</tbody>

 

[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.