Extracting data from one table format and putting into a different viewing format

For the week 1 fixtures, put this formula in H2:

=IF(ColorIndex(F2)=35,"H_"&COUNTIF(H$1:H1,"H*")+1,"A_"&COUNTIF(H$1:H1,"A*")+1)

and copy down to the bottom of your list. Then you can have this formula in K3 for the home teams:

=INDEX(F:F,MATCH("H_"&J3,H:H,0))

and this in L3 for the away teams:

=INDEX(F:F,MATCH("A_"&J3,H:H,0))

and copy both these down.

Then have a similar set up for week 2. I've shown this in the attached file.

Hope this helps.

Pete