Formula to return the heading if its cell highlighted in certain colour

[Linvin]

Hi
I was wondering if there is a formula to help me with this data to return the heading of a column if one of its cells is highlighted in a certain colour. The highlights are placed randomly. I have attached a sample data here if it helps. The data is in Table A, and I'm trying to achieve an outcome as shown in Table B.

Blue for Term 3
Green for Term 2
Beige for Term 1

Thanks!

[AliGW]

Formulae cannot use cell formatting (including colour) as criteria. This means that you cannot do with a formula what you have shown in the sample - sorry. Whether or not it can be done with VBA, I don't know, but shall I move the thread for you to that section?

[Linvin]

Hi Ali
Thanks so much for getting back to me. I'm not familiar with VBA so would really prefer to use formula if at all possible.
If I updated the colours into a, b, c, would it help? I have attached the updated sample again. Thanks!

cheers
Linvin

[Gregb11]

Given your spreadsheet, why doesn't the row for "E" have Placement 4 under Term 1, and why not Placement 1 under Term 3 on row "M" or (row 34), etc.?

[Linvin]

Hi yes they should have all those you mentioned, so sorry for the confusion, I only added 3 for an example but I think you got the idea.

[Gregb11]

I put the letters "a", "b", and "c", in cells B19:D19, then I entered this formula into Cell B22. Then copy over and down:

=IFERROR(INDEX($B$2:$K$2,,MATCH(B$19,$B3:$K3,0)),"")

[Linvin]

That looks so simple yet it works like a charm. Thanks so much for your help. I need to remember this index-match formula better.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.