Help with Offset/Lookup

[Sweenster]

I have an Excel table which contains 2 rows per person showing the
latest score and then below it the avg for the year (See example
below).
I need a formula that will lookup the ID and then return the yearly avg
for each person so that when I use filldown only the YearAvg score is
returned. I have tried using a combo of Offset and match and seem to be
close(ish!) but still can not get it to work.

=OFFSET(Score,MATCH(D6,ScoreCard!C4:C14,0),0)


ID TimePeriod Col_1 Score
1234 Nov x 5
1234 YearAvg x 4
4567 Nov x 7
4567 YearAvg x 6

Any help would be greatly appreciated.
Cheers

 

[vezerid]

Sweenster,
you are right, you are very close. I assume Score is the name of the
cell with the label "Score". If so, you just need to add 1 to MATCH().

=OFFSET(Score,MATCH(D6,ScoreCard!C4:C14,0)+1,0)

HTH
Kostis Vezerides