Summing across multiple sheets by specific name, VLOOKUP?

I have Mac:2011 version of Excel.
Here's what I'm trying to do...
I have 5 sheets, 1 for each week of a typical month, and then a 5th sheet in which I want to add cumulative values.

In the first four sheets, column A is the name of a person. Columns B through H are daily performance values. Column I is the weekly sum of columns B-H. I am sorting each of these first 4 sheets by column I, from highest-lowest; so a person's row position changes from sheet 1 through sheet 4, based on how well they did in comparison to other people.

I should also mention that a person may or may not appear in each sheet for weeks 1-4. They might be there in week 1, miss week 2, and be back for week 3 and 4, etc.

Now, in sheet 5, the 'cumulative values sheet', is where I'm having difficulty. Column A is again, where the names of people should go. Columns B, C, D & E are what I want to be the sum values from Weeks 1-4. And finally, Column F is a sum of these columns B-E, yielding a monthly total. Here's the logic of what I want to do...

Part 1:
Look at the name of the person in [Sheet 1:Column A through Sheet 4:Column A]...
If a person's name from [Sheet 1:Column A through Sheet 4:Column A] doesn't appear in sheet 5, column A, copy that name to sheet 5, column A. Otherwise, move on to part 2...

Part 2: Look-up the week 1 value of the Sheet 5: Column A name, from sheet 1, and copy the associated sum value of that name (sheet1:column I) to Sheet 5:Column B. Repeat for Sheet/Week 2 value to Sheet 5:Column C... Week 3 to Column D, and Week 4 to Column E...

And finally, sum the values of Sheet 5, Columns B-E to column F. Sort highest-lowest.

No issues with the summing function, but I'm not sure the best way to achieve 'Part 2'. I've been fooling around with VLOOKUP, and have had some success with =VLOOKUP(A2,Week1!A2:I26,9) for column B, =VLOOKUP(A2,Week2!A2:I26,9) for column C, etc... but it's only performing properly for my first row, and then I mostly get #N/A. So what am I missing? Or is there an alternate function that would achieve this in an easier fashion?

Any advice would be appreciated. I'm a tinkerer... but my brain has tinkered on this about as far as it can.

Mostly because the excel file is being used and presented to an audience in real-time, as their values are coming in. The spreadsheet is for a weekly 'trivia night' in a local restaurant, and columns B-H are a person's score on each of the 7 rounds that night. Four tabs for the 4 weeks are being used because, while it is more complex on my end to figure out the formula, it is simpler and more pleasing to the eye of the audience to see the scores for just the night that they're playing. At the end of the night, their score is added to their monthly cumulative score, and they like to know how they're doing overall, toward the monthly competition, so I'd ideally like to just switch to sheet 5 after we finish whatever week/sheet we're currently in, and have the cumulative scores automatically, already tallied... rather than fumble around with manually entering the scores, etc.

It's a trivial, problem, I realize :P

Attached is the month of June, for what I want to do. You'll notice a hefty bit of conditional formatting within the Week 1 through Week 5 sheets (highlighting high/low scores, etc). All of that is fluff to make it look nice. And I'm pretty satisfied with how it is functioning.

You'll see in the 'Cumulative' worksheet what I'm having difficulty with. I've typed in the names of three of the names of teams from previous sheets, into column A of this sheet. Ideally, I'd like column A in 'Cumulative' sheet to look at previous column A's in Weeks 1 through 5, and add the name to this column if it doesn't already appear.

Then, after I have a list of all teams that participated throughout the month in column A
...I want 'Cumulative' sheet, Column B to look at the corresponding row name in column A...
... find that name in 'Week 1' sheet, and...
... copy the corresponding column I value from the row in the Week 1 sheet in which that name appears to 'Cumulative' sheet column B.

Then repeat for column C in Cumulative sheet, for Week 2. Then column D for Week 3, etc...
I haven't tinkered with macros before, so that would be an adventure for me. But if it's the only way, I'll have to learn how to I suppose. Thanks again.

After tinkering most of last evening, I've successfully implemented the second half of what I was trying to do...

"Then, after I have a list of all teams that participated throughout the month in column A
...I want 'Cumulative' sheet, Column B to look at the corresponding row name in column A...
... find that name in 'Week 1' sheet, and...
... copy the corresponding column I value from the row in the Week 1 sheet in which that name appears to 'Cumulative' sheet column B.

Then repeat for column C in Cumulative sheet, for Week 2. Then column D for Week 3, etc...
I haven't tinkered with macros before, so that would be an adventure for me. But if it's the only way, I'll have to learn how to I suppose. Thanks again."

Some variation of... =IF(ISERROR(VLOOKUP(A2,Week1!$A2:$I26,9,FALSE)),"-",VLOOKUP(A2,Week1!$A1:$I25,9,FALSE))
...for each cell in the 'Cumulative Sheet'. So as it stands now, if I type in any of the team names from column A of the previous sheets, the cells in 'Cumulative' sheet automatically fill in the corresponding scores.

The only other thing I'd like to implement, is the first part... "Ideally, I'd like column A in 'Cumulative' sheet to look at previous column A's in Weeks 1 through 5, and add the name to this column if it doesn't already appear."

I think I've figured out how to do it if I was only looking at an array from a single sheet, using something similar to =INDEX($A$2:$A$20, MATCH(0, COUNTIF($B$1:B1, $A$2:$A$20), 0))

I'm having difficulty figuring out how to look at A2:A26 from sheets Week1 through Week5, to perform the INDEX function and only return unique entries. Any suggestions on that one?