Can anyone help me with a tricky formula?
I have 3 columns of data with a heading above them. But every 20 to 30 rows (it changes) the heading changes. the heading can only be one of three different things. lets call them Red, Green and Blue. So eg Green appears in column A for 20 rows, then swaps to say column B for 24 rows, then say back to column A for 30 rows. the Red and Blue are in the other columns.
what I want to do is return all green values to column D, all red to E and blue to F.
At each change in the data, the headings do change too, so I need a formula that matches the text at the top of my master columns (D:F) with the text in this header row, and returns the value in the row.

I will accept a formula where I have to change something manually every 20-30 rows if this part cannot be automated.

Many thanks
Gareth