Return a Column Heading using a formula

[MrOverheads]

I have no idea how to use VBA so looking for a formula solution.

Data Looks Like:

Ref 1	Ref 2	Ref 3	Ref 4	Ref 5	Ref 6	Ref 7	Ref 8
Heading 1	Heading 2	Heading 3	Heading 4	Heading 5	Heading 6	Heading 7	Heading 8	Reference
No	No	No	No	No	Yes	n/a	n/a	?
No	No	No	Yes	n/a	n/a	n/a	n/a	?
No	No	No	No	Yes	n/a	n/a	n/a	?
No	No	Yes	No	n/a	n/a	n/a	n/a	?

<colgroup><col span="8"><col></colgroup><tbody>
</tbody>

So what I'm trying to do is put a formula where the "?"'s are. The result should be the red description at the top of the column where the Blue "Yes" is for each row. i.e. when finished table should look like:

Ref 1	Ref 2	Ref 3	Ref 4	Ref 5	Ref 6	Ref 7	Ref 8
Heading 1	Heading 2	Heading 3	Heading 4	Heading 5	Heading 6	Heading 7	Heading 8	Reference
No	No	No	No	No	Yes	n/a	n/a	Ref 6
No	No	No	Yes	n/a	n/a	n/a	n/a	Ref 4
No	No	No	No	Yes	n/a	n/a	n/a	Ref 5
No	No	Yes	No	n/a	n/a	n/a	n/a	Ref 3

<tbody>
</tbody>

Doens't seem liek it would be ahrd to do, but my complex combination of Vlookup, hlookup and offsets isn't working must be an easier way i.e. Find the column with "Yes" and return the contents of cell in Row 1 of that column.

Hope somebody can help please.

 

[bbott]

Welcome to the board.

Assuming your data starts in A1, try this in I3 and copy down:

=INDEX($A$1:$H$1,,MATCH("Yes",A3:H3,0))

Or, if you literally just want to return the reference to the column number:

="Ref "&MATCH("Yes",A3:H3,0)

 

[MrOverheads]

Brilliant so simple, must be having a bad day. Thanks again.

 

[bbott]

Brilliant so simple, must be having a bad day. Thanks again.

You're welcome.