XL formula to lookup 2nd row for each ID 1

[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 (2nd row) for each person so that when I use filldown only the YearAvg score is returned.

Have tried using Offset and Match and thought I was close(ish!) but still can not get it to work!
I have tried using a
=OFFSET(Score,MATCH(D6,ScoreCard!C4:C14,0),0)


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

Any help would be greatly appreciated.
Cheers

 

[xlbo]

If there are ALWAYS 2 rows per name then this should work:

INDEX(B:B,MATCH(D2,A:A,0)+1)

where list of IDs in column A and list of scores in column B for an ID entered into D2

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

How is the rest of the data stored? If a given employee's info is all in one row and you want a running average in the row below, then you could just use something like
=average($a2:b2)
and fill to the right.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

if your ID's are in column H
[tt]
=sumproduct((ID=H1)*(TimePeriod="YearAvg")*(Score))
[/tt]
assuming that your RANGES are named

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[SkipVought]

The THREE musketeers, vying for the fastest sword????? ;-)

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[AnotherHiggins]

I have an Excel table which contains [red]2 rows per person[/red] 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 (2nd row) for each person [red]so that when I use filldown[/red] only the YearAvg score is returned.

The two sections I marked in red seem contradictory. How is your data currently stored - in ROWS (left to right) or COLUMNS (up and down)?

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[xlbo]

seems to me like they have a data sheet with 2 rows per id and a summary sheet with a list of IDs. The INDEX/MATCH formula I posted was created with that setup in mind

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Sweenster]

The set up is as XLBO describes and the Index/Match formula works a treat.

Thanks Geoff and other for response.

 

[AnotherHiggins]

Nice reading, Geoff.

Sweenster, Please consider clicking the link at the lower left of Geoff's post - the one that says, "Thank xlbo for this valuable post". That will award him a little purple star and will let other visitors know that your question was properly answered.

Skip: I finally beat you. But with the wrong answer.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.