Vlookup and Index...formula help

[Jambruins]

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.

 

[Bob Phillips]

=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]

I pasted your formula in but I get #N/A. Do I have to modify your formula at
all or should it work right away? Thanks.

 

[Jambruins]

Bob, any idea why I am getting a #N/A error? Thanks.

 

[Stephen]

Which part of the formula is not working: MATCH or INDEX?
To find out, highlight (exactly)
MATCH(AA3,INDIRECT(W3&"!AA:AA"),0)
in the formula bar and press F9. If this shows a number, this part of the
formula is working; if it shows #N/A it is not finding a match. Press ESC to
get out of this mode.

Your MATCH is looking for an exact match on a text string. If AA3 and the
entry in NYR!AA:AA differ in any respect (even by a space character), it
will fail to find a match.

 

[Jambruins]

Stephen, I just check the match by highlighting it and hitting F9. That came
up as #N/A. I checked the text in cell AA3 and the text in the corresponding
tab and it appears to be the same. I used the code formula on both cells and
they both were 68 which I am assuming means they are the same. Any ideas?
Thanks.

 

[Jambruins]

I just tried using vlookup (=VLOOKUP(AA3,NYI!F:G,2,0) and it worked so the
text is the same.

 

[Stephen]

I suppose, to be absolutely sure they are the same, you could copy AA3 and
paste into the appropriate cell in NYR!AA:AA (or vice-versa), and see if
this makes any difference.

The other thing is to check that W3 contains exactly the name of the other
sheet (again, no spaces, etc.).
If you highlight
W3&"!AA:AA"
in your formula and hit F9, do you see the reference NYR!AA:AA exactly? At
this point you could use CTRL-C to copy the reference, then use
Edit > Go To
and use CTRL-V to paste the reference, and see if it takes you to the
correct column on the correct sheet.

 

[Jambruins]

Stephen,
When I highlight W3&"!AA:AA" and hit F9 I see NYR!AA:AA but when I use the
go to option it tells me reference is invalid. Any ideas? Thanks for all
the help.

 

[Stephen]

But why "F:G"? The formula you said you were using has "AA:AA".

 

[Dave Peterson]

Are you sure that there's a match for AA3 in column AA of the worksheet with the
name in W3?

I bet that there isn't an exact match.

 

[Jambruins]

Stephen, Dave and Bob, thanks for all your help. I found my mistake. I had
to change the AA:AA to F:F. I describe it wrong in my original question.
Thanks a lot.

 

[Stephen]

Try
Edit > Go To
and just type in "NYR!A1" (without the quotes).
If this is invalid, I suspect you don't have a worksheet named "NYR".
Perhaps it's "NYR " or " NYR" (note the spaces) or some such.
However, if this does take you to NYR!A1, type in "NYR!AA:AA" and see if
this is valid.