I am struggling with a IF and VLOOKUP (I'm not sure if this is the best option, perhaps someone may have a better suggestion).

What I am trying to do is return a value from a list to another list, where the names are matched.

I have one workbook with two worksheets. On the first worksheet I have a list of 12 names using 12 rows, say A1:A12 (this will never change), and on the second worksheet I have one column say Column A1:A6 with 6 names and in column C1:C6, I have the remaining 6 names. This is one team playing another team. In the next 12 columns, the first 6 columns (D1:J6) are scores ect for the team in column A, and the foloowing 6 columns (K1:O6) relate to the team named in column C.

What I want to do is use the formula which takes the name from the list on the first worksheet, matches it with the name in column A of the second worksheet, if it finds a match, then returns a value from column D:J, if it doesn't find a match in column A, looks in Column C, if it finds a match in column C returns a value from columns K:O. There will always be a match in either column A or C

Is this possible please?

Any help given is much appreciated

Kind Regards

Peter