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.

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

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" wrote:

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, any idea why I am getting a #N/A error? Thanks.

"Bob Phillips" wrote:

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

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" wrote in message
...
Bob, any idea why I am getting a #N/A error? Thanks.

"Bob Phillips" wrote:

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

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.

"Stephen" wrote:

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" wrote in message
...
Bob, any idea why I am getting a #N/A error? Thanks.

"Bob Phillips" wrote:

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

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

"Jambruins" wrote:

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.

"Stephen" wrote:

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" wrote in message
...
Bob, any idea why I am getting a #N/A error? Thanks.

"Bob Phillips" wrote:

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

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" wrote in message
...
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.

"Stephen" wrote:

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" wrote in message
...
Bob, any idea why I am getting a #N/A error? Thanks.

"Bob Phillips" wrote:

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

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" wrote:

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" wrote in message
...
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.

"Stephen" wrote:

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" wrote in message
...
Bob, any idea why I am getting a #N/A error? Thanks.

"Bob Phillips" wrote:

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

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

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

"Jambruins" wrote:

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.

"Stephen" wrote:

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" wrote in message
...
Bob, any idea why I am getting a #N/A error? Thanks.

"Bob Phillips" wrote:

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

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 wrote:

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

"Bob Phillips" wrote:

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




--

Dave Peterson

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.

"Dave Peterson" wrote:

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 wrote:

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

"Bob Phillips" wrote:

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




--

Dave Peterson

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.

"Jambruins" wrote in message
...
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" wrote:

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" wrote in message
...
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.

"Stephen" wrote:

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" wrote in message
...
Bob, any idea why I am getting a #N/A error? Thanks.

"Bob Phillips" wrote:

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