Clarification:
Something along these lines should work (both untested, apologies):
=VLOOKUP($W$5,INDIRECT("'[2004 Rush Offensive Stats.xls]Game
"&(RIGHT(B47,2))&"'!$B$2:$AO$100"),37,FALSE)

The above suggestion presumes that "RIGHT(B47,2)" will evaluate to a number
like: 47

(Think there was an additional parens around "RIGHT(B47,2)" which was not
necessary - missed out earlier. This can be removed.)

If however, as per line in the original post:
The cell content of B47 is in another workbook called "2004 Individual
Stats" [corrected for typo] ...

then we might need another INDIRECT inside RIGHT(...),

For example, if you have in A2: B47
then you could out in say, B2:
=VLOOKUP($W$5,INDIRECT("'[2004 Rush Offensive Stats.xls]Game
"&RIGHT(INDIRECT("'[2004 Individual
Stats.xls]Sheet1'!"&A2),2)&"'!$B$2:$AO$100"),37,FALSE)

where cell B47 in Sheet1 in book: 2004 Individual Stats.xls
contains say: 447, or T47

RIGHT(INDIRECT("'[2004 Individual Stats.xls]Sheet1'!"&A2),2)
will then evaluate to: 47

and the formula for the table array part:

INDIRECT("'[2004 Rush Offensive Stats.xls]Game "&RIGHT(INDIRECT("'[2004
Individual Stats.xls]Sheet1'!"&A2),2)&"'!$B$2:$AO$100")

will resolve to:

'[2004 Rush Offensive Stats.xls]Game 47'!$B$2:$AO$100

which is the table array in your original formula:
=VLOOKUP($W$5,'[2004 Rush Offensive Stats.xls]Game 47'!$B$2:$AO$100,37,FALSE)

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----