Retrieve latest value from mulitple columns

Clearly I have a lot to learn in excel. From rooting around the other threads in the forum and tips I think my question could involve lookup, match, if, index and more. Unforunately i've not come across the answer yet or more accurately i'm not clever enough to recognise the answer.

My problem I hope is best explained in the attached example worksheet. But if not....

I have a list of teams in 2 columns (columns A and B). These teams play matches against each other. The teams repeat themselves but vary in their order in which they play and whether they are home or away; i.e. appear in column A or B.

Each team has a points brought forward value in column C or D which correspond to the teams in column A and B respectively. They also then have a carried forward points value in column E or F produced after the points from the current game (not shown in the attached file).

I need to be able to find a formula which will retrieve the latest (last match) carried forward value for a team and enter it into the brought forward cell. This is made difficult because the team may last appear in columns A or B and there is no logic as to when they last played. It is easy enough to use lookup to find the value from the first row of an array but i cannot work out how to find the value in the last row.

Here's hoping someone does? Thanks.

In A14: =INDEX(Data,MAX((Data=A$13)*ROW(Data)),COLUMNS(Data)-(MATCH(A$13,RngA,0)=MAX((Data=A$13)*ROW(Data))))

ctrl+shift+enter, not just enter
copy A14 to B14

thanks for your help TM. Unfortunately I cannot get the formulas to work in cells C13 and D13 which is where I need them. I get a #value error when i have the following formula in call C13:

=INDEX(Data,MAX((Data=A$13)*ROW(Data)),COLUMNS(Data)-(MATCH(A$13,RngA,0)=MAX((Data=A$13)*ROW(Data))))

Sorry I dont really have any experience with Data! When I have the {} surrounding the forumal i dont even get a result. Excel just shows the formula.

Im out of depth I know but I really appreciate if you can help again.

did you try it in tm's sheet?
i downloaded it and copied a14 formala into c13 and it works ok for me

thanks martin, but dont you get a circular reference if you just copy?

try as i might i just cannot get the formula to copy correctly. if i just copy the cell into c13 i get a circular reference to c13. when i change the cell reference from c13 to a13 i get the #value error. ive tried dragging the formula, copy/paste special, copying into the cell, copying into the formula bar.

when i use ctrl+shift+enter nothing seems to happen.

i also want to be able to copy the cell formulas down the rest of the columns C and D as well but i cannot get this to work either!

im pleased to say i have the ctr+shift+enter working. now i just need to work out how to copy or drag the cells into the rest of the worksheet?

if anyone can help with this please let me know.

hahaha, ive done it. thank you all very much. this has saved me so much time.

ok, i spoke too soon. is it possible to change the Data range and RngA to reference only and all the cells above the current row?

it seems that when i drag down the formulas the array sets do not change.

does anoyone know whether this would be possible or is it beyond excels bounds?

Hello Paul,

Try this formula in C13

=SUM((ROW($A$2:$B12)=MAX(IF($A$2:$B12=A13,ROW( $A$2:$B12))))*($A$2:$B12=A13)*$E$2:$F12)

Confirm with CTRL+SHIFT+ENTER and then copy to D13 and down both columns. The range will alter automatically to include all rows from 2 to the one above the formula

Dear Daddylonglegs,

You really have made my day, week and month. I've managed to take your formula into my spreadsheet and adapt it without trouble.

A sincere and very big thank you.

Paul.