=INDEX(INDIRECT(W3&"!W:W"),MATCH(AA3,INDIRECT(W3&" !AA:AA"),0))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Jambruins" wrote in message
...
I have a bunch of tabs in my spreadsheet and each one is named after a team
(i.e. BOS, NYR, VAN, etc.). In column AA I have text and in column W I
have
team names. I would like a formula to lookup the text in column AA in the
tab of the team that is in column W. For example in cell AA3 I have Dec
18-Pittsburgh and in cell W3 I have NYR. I would like a formula to find
the
text Dec 18-Pittsburgh in the NYR tab and give me the value of the cell 4
columns over. Thanks in advance.