Getting a value not available error on a vlookup call yet the value is visible to the eye

[ssort]

Hi all, Im in a football pool at work, and the guy that runs it asked for my help since I mess with excel some. What he wanted was a way to get totals based on font color, I did this easily using code I found on the internet by creating a vb module and inputting the code I found, and since I did that so easily, he asked me if I could then sort the totals somehow automatically. Well I show him since it was in table like form, you just had to click the cell in the totals column and choose sort, well now hes thinking im a guru since I solved his problem so easy.

So now he asks if I could add grand totals for the year for each person, grabbing the total from each week (each week is on a separate workbook sheet in the same workbook), so I decided to use named ranges for the name and score on each page and used the names week1 to week17 to reference them, I then did a vlookup call to look up the name of the player and the corresponding score from each page and added them together.

The problem I've run into is two fold, first you have to make sure the sort order is in the players alphabetical order first for each weeks page (bit of a hassle but I think he and I can live with it), but the formula I used for adding the vlookup values works on every players total but the first for some reason and for the life of me I cant figure it out. Ive even deleted out all the addition for the first player and just tried to do a vlookup by itself for the first player "Aaron" on just the first weeks results just to see if I can get his score for the first week only so im only only having to deal with the value of one cell, but for some reason it fails also, the error says value not available.

Ive attached the excel workbook, if someone could help me out I would appreciate it as what I thought would be a 20min project at most is now on my 4th hour and has now spilled into my home time and im pulling my hair out trying to fix this, as I know its something simple im missing, but for the life of me, I cant find it. The problem is in cell B2 of the last worksheet (grand totals), you can see in B3 the whole formula that I want to use in B2, but I culled it down to just the first step in B2 as that seems to be where the problem is. Once again any help is appreciated!

[FDibbins]

THis works...
=VLOOKUP($A2,Week1,2,0)

vlookup has 4 arguments
A2...what to find
Week1...range to search in
2...column to return answer from
0...indicating an exact match is required

[ssort]

Thank you so much! I did not even think to use the optional parameter and still dont know for sure why it didnt work as I copy and pasted the list of names from the first sheet, but its working now and thats all that counts! You are a god among men, and I thank you from the bottom of my heart!!!

[FDibbins]

On a side note, you can simplify that addition across your sheets...

1st, a list of all your sheet names and give that range a name (I used sheettab)

Then, use this, copied down Grand Totals B2...
=SUMPRODUCT(SUMIF(INDIRECT("'"&weektab&"'!A2:A50"),A2,INDIRECT("'"&weektab&"'!B2:B50")))
(Courtesy of Tony Valco)