Lookup and return over multiple columns

[thechump]

Hi, I am a teacher and need to record the results from collaborative paired projects for my class. The idea being that I am able to see the effect of different partnerships on test results. I also need to set incremental targets based on personal attainment. I am struggling to write the formulas required to look up a value over two columns and return a value from on of two columns. For example -

Week 1 testing

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]Child 1[/TD]
[TD]Child 2[/TD]
[TD]Target child 1[/TD]
[TD]Target child 2[/TD]
[TD]Results child 1[/TD]
[TD]Result child 2[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Frank[/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]

Week 2 testing - I want to be able to enter a formula in columns C and D that lookup the name in column A/B and return their results from E/F. The partners have swapped and I can add a calculation to add an increment to the target if I can get the lookup working.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]Child 1[/TD]
[TD]Child 2[/TD]
[TD]Target child 1[/TD]
[TD]Target child 2[/TD]
[TD]Results child 1[/TD]
[TD]Result child 2[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Bill[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frank[/TD]
[TD]Peter[/TD]
[TD]14[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I'm struggling to understand if this is possible and also how I could return the last known results when I have multiple weeks of results.

 

[Nine Zero]

Hello.... try this out in cell C2 which is where your result 12 for Joe for week 2 is at

=IFERROR(INDEX($E$2:$E$3,MATCH(A10,$A$2:$A$3,0)),INDEX($F$2:$F$3,MATCH(A10,$B$2:$B$3,0)))

 

[wideboydixon]

As above, I'm not 100% sure I got the requirements right and I'm sure there's an easier way to do this but the issue is with finding the right column for a child.

Book1
	A	B	C	D	E	F
1	Child 1	Child 2	Target Child 1	Target Child 2	Results Child 1	Results Child 2
2	Joe	Frank			12	14
3	Bill	Peter			10	13
4	Joe	Bill	12	10	13	9
5	Frank	Peter	14	13	10	16
6	Peter	Joe	16	13
7	Bill	Frank	9	10
Sheet1

Cell Formulas
Range	Formula
C4	{=IF(INDEX($A$1:$B3,MAX(IF($A$2:$B3=A4,ROW($A$2:$B3))),1)=A4,INDEX($E$1:$E3,MAX(IF($A$2:$B3=A4,ROW($A$2:$B3)))),INDEX($F$1:$F3,MAX(IF($A$2:$B3=A4,ROW($A$2:$B3)))))}
D4	{=IF(INDEX($A$1:$B3,MAX(IF($A$2:$B3=B4,ROW($A$2:$B3))),1)=B4,INDEX($E$1:$E3,MAX(IF($A$2:$B3=B4,ROW($A$2:$B3)))),INDEX($F$1:$F3,MAX(IF($A$2:$B3=B4,ROW($A$2:$B3)))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

That will retrieve the latest result for a child and put it in the target but it's not very efficient as it runs the same array formula three times.

WBD

 

[thechump]

Thank you so much....that works fantastically well!

Is there a way of the formula selecting the last score occurrence for each child as the weeks go by. I can see that you have used static references for the range. Is there a way of making that dynamic over the last say 20 records (this should cover 2 weeks worth of testing so if a child is absent one week, it will still pull their last score.)

 

[wideboydixon]

Similar solution without array formula:

Book1
	A	B	C	D	E	F
1	Child 1	Child 2	Target Child 1	Target Child 2	Results Child 1	Results Child 2
2	Joe	Frank			12	14
3	Bill	Peter			10	13
4	Joe	Bill	12	10	13	9
5	Frank	Peter	14	13	10	16
6	Peter	Joe	16	13
7	Bill	Frank	9	10
Sheet1

Cell Formulas
Range	Formula
C4	=IF(SUMPRODUCT(MAX(ROW($A$1:$A3)*($A$1:$A3=A4)))>SUMPRODUCT(MAX(ROW($B$1:$B3)*($B$1:$B3=A4))),LOOKUP(2,1/($A$1:$A3=A4),$E$1:$E3),LOOKUP(2,1/($B$1:$B3=A4),$F$1:$F3))

Copy C4 formula across to D4 and then down as far as necessary.

WBD

 

[thechump]

Absolutely fantastic!

Thank you - you have saved me hours of googling!!!!