Matching names in row with date in column -corresponding result

[=HPSF=RMP]

This goes beyond the index match.
I have a sheet named "scores" that has a list of "names" in rows A5:A16 and a "last month" in C5:C16 & "Previous month" in D5:D16. B2 has the current month listed.


I have a seperate sheet named "Monthly scores".
The name to match is A:A.
The month to match is the columns from E-Z.

Is it possible to match the name in A:A with the names on the "scores" sheet? Then match the month in B2 on the "scores" sheet to find the corresponding score?

I want the result to post in the "last month" as a -1
and the previous month as a -2

Book1
	A	B	C	D	E	F	G	H	I
2	AdjustedName	Agent	AverageRating	Manager	Jan-08	Feb-08	Mar-08	Apr-08	May-08
3	JohnSmith	Smith,John	2.50	jim	3	2	3	2
2008 Evals

If B2 on the scores sheet is May 08.
I want to find John Smith and bring back the March 08 result in the last month, and the Feb 08 result in the Previous month.

June Evals.xls
	A	B	C	D
2	EvaluationPeriod	May-08
3	DeliveredDate	7/10/2008
4	Collector	CurrentMonth	LastMonthRating:	PreviousMonthRating:
5	JohnSmith	4.23	#N/A	#N/A
Scores

Is this asking too much of Excel?

thanks

 

[DonkeyOte]

No this is pretty straightforward.

Assuming your layout on sample the below formula would work for Current, Last & previous values.

The below would go in SCORES!B5 - then copy across for B6 & B7

=INDEX('MONTHLY SCORES'!$A:$Z,MATCH($A5,'MONTHLY SCORES'!$A:$A,0),MATCH($B$2,'MONTHLY SCORES'!$1:$1,0)+(2-COLUMN(B5)))

 

[DonkeyOte]

sorry, just realised current is pulling in from another source

so enter the below into B6 and copy into B7 (leaving B5 untouched)

=INDEX('MONTHLY SCORES'!$A:$Z,MATCH($A5,'MONTHLY SCORES'!$A:$A,0),MATCH($B$2,'MONTHLY SCORES'!$1:$1,0)+(2-COLUMN(B<b>6</b>)))

 

[=HPSF=RMP]

=INDEX('[Monthly Evaluation Scores.xls]2008 Evals'!$A:$Z,MATCH($A5,'[Monthly Evaluation Scores.xls]2008 Evals'!$A:$A,0),MATCH($B$2,'[Monthly Evaluation Scores.xls]2008 Evals'!$1:$1,0)+(2-COLUMN(B5)))

doesnt seem to work for me.

 

[=HPSF=RMP]

I'm wanting the results to go into C5 & D5 on the Scores sheet.

 

[DonkeyOte]

assuming you're entering the below in C5

=INDEX('[Monthly Evaluation Scores.xls]2008 Evals'!$A:$AZ,MATCH($A5,'[Monthly Evaluation Scores.xls]2008 Evals'!$A:$A,0),MATCH($B$2,'[Monthly Evaluation Scores.xls]2008 Evals'!$2:$2,0)+(2-COLUMN(C5)))