VLOOKUP Returns Erroneous Value When Control Data is Variable
 

I have a workbook that uses VLOOKUP on the Summary sheet to find values
associated with a Name and Week #. The worksheet I'm trying to capture the
data from looks like:
B C D E
Name | Week 0 | Week 1 | Week 2 | etc

Week 0 indicates the beginning and no real values are entered. My formula is:
=VLOOKUP(A6,Points!$B:$AB,$B$1+1,FALSE)

The problem is the result expected for Week 0 is "0" but instead it returns
the Name.

I tried MATCH as so:
=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!C2:AB2,0),FALSE)

Same results received. Any number other than 0 in $B$1 returns the correct
result, but I want the initial sheet to show the corrct values.

Any help will be appreciated.

VLOOKUP Returns Erroneous Value When Control Data is Variable
 

Hi!

Not sure I follow you........

If you enter 0 in B1 (or, if B1 is empty) then the formula is:

=VLOOKUP(A6,Points!$B:$AB,1,FALSE)

And returns the lookup_value (if found) from the first column of the
lookup_array.

Week 0 is in the second column of the table_array so you would need to enter
1 in B1.

I tried MATCH as so:
=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!C2:AB2,0),FALSE)

If you use Match to find the column then in B1 you'd have to enter: Week n,
then change the range to:

=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!B2:AB2,0),0)

Biff

"The Hawk" wrote in message
...
I have a workbook that uses VLOOKUP on the Summary sheet to find values
associated with a Name and Week #. The worksheet I'm trying to capture
the
data from looks like:
B C D E
Name | Week 0 | Week 1 | Week 2 | etc

Week 0 indicates the beginning and no real values are entered. My formula
is:
=VLOOKUP(A6,Points!$B:$AB,$B$1+1,FALSE)

The problem is the result expected for Week 0 is "0" but instead it
returns
the Name.

I tried MATCH as so:
=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!C2:AB2,0),FALSE)

Same results received. Any number other than 0 in $B$1 returns the
correct
result, but I want the initial sheet to show the corrct values.

Any help will be appreciated.

VLOOKUP Returns Erroneous Value When Control Data is Variable
 

Thanks. It fixed that problem. I then tried to modify it for another
situation and it didn't work. Following is the situation:

B C D E-H I J AB
Name | Week -5 | Week -4 | ... | Week 1 | Week 2 | etc...

My modification was:

=VLOOKUP(A6,Reg_Scores!$I:$AB,MATCH(Summary!$B$1,R eg_Scores!$I$2:$AB$2,0),0)

Where is my thought process failing?

Thnaks...

"Biff" wrote:

Hi!

Not sure I follow you........

If you enter 0 in B1 (or, if B1 is empty) then the formula is:

=VLOOKUP(A6,Points!$B:$AB,1,FALSE)

And returns the lookup_value (if found) from the first column of the
lookup_array.

Week 0 is in the second column of the table_array so you would need to enter
1 in B1.

I tried MATCH as so:
=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!C2:AB2,0),FALSE)

If you use Match to find the column then in B1 you'd have to enter: Week n,
then change the range to:

=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!B2:AB2,0),0)

Biff

"The Hawk" wrote in message
...
I have a workbook that uses VLOOKUP on the Summary sheet to find values
associated with a Name and Week #. The worksheet I'm trying to capture
the
data from looks like:
B C D E
Name | Week 0 | Week 1 | Week 2 | etc

Week 0 indicates the beginning and no real values are entered. My formula
is:
=VLOOKUP(A6,Points!$B:$AB,$B$1+1,FALSE)

The problem is the result expected for Week 0 is "0" but instead it
returns
the Name.

I tried MATCH as so:
=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!C2:AB2,0),FALSE)

Same results received. Any number other than 0 in $B$1 returns the
correct
result, but I want the initial sheet to show the corrct values.

Any help will be appreciated.

VLOOKUP Returns Erroneous Value When Control Data is Variable
 

B C D E-H I J
AB
Name | Week -5 | Week -4 | ... | Week 1 | Week 2 | etc...
My modification was:
=VLOOKUP(A6,Reg_Scores!$I:$AB,MATCH(Summary!$B$1, Reg_Scores!$I$2:$AB$2,0),0)
Where is my thought process failing?

You've defined the lookup_array as $I:$AB so the formula looks for the
lookup_value (A6) in column I not column B.

Try this:

=VLOOKUP(A6,Reg_Scores!$B:$AB,MATCH(Summary!$B$1,R eg_Scores!$B$2:$AB$2,0),0)

Biff

"The Hawk" wrote in message
...
Thanks. It fixed that problem. I then tried to modify it for another
situation and it didn't work. Following is the situation:

B C D E-H I J
AB
Name | Week -5 | Week -4 | ... | Week 1 | Week 2 | etc...

My modification was:

=VLOOKUP(A6,Reg_Scores!$I:$AB,MATCH(Summary!$B$1,R eg_Scores!$I$2:$AB$2,0),0)

Where is my thought process failing?

Thnaks...

"Biff" wrote:

Hi!

Not sure I follow you........

If you enter 0 in B1 (or, if B1 is empty) then the formula is:

=VLOOKUP(A6,Points!$B:$AB,1,FALSE)

And returns the lookup_value (if found) from the first column of the
lookup_array.

Week 0 is in the second column of the table_array so you would need to
enter
1 in B1.

I tried MATCH as so:
=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!C2:AB2,0),FALSE)

If you use Match to find the column then in B1 you'd have to enter: Week
n,
then change the range to:

=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!B2:AB2,0),0)

Biff

"The Hawk" wrote in message
...
I have a workbook that uses VLOOKUP on the Summary sheet to find values
associated with a Name and Week #. The worksheet I'm trying to capture
the
data from looks like:
B C D E
Name | Week 0 | Week 1 | Week 2 | etc

Week 0 indicates the beginning and no real values are entered. My
formula
is:
=VLOOKUP(A6,Points!$B:$AB,$B$1+1,FALSE)

The problem is the result expected for Week 0 is "0" but instead it
returns
the Name.

I tried MATCH as so:
=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!C2:AB2,0),FALSE)

Same results received. Any number other than 0 in $B$1 returns the
correct
result, but I want the initial sheet to show the corrct values.

Any help will be appreciated.

VLOOKUP Returns Erroneous Value When Control Data is Variable
 

Thank You!

"Biff" wrote:

B C D E-H I J
AB
Name | Week -5 | Week -4 | ... | Week 1 | Week 2 | etc...
My modification was:
=VLOOKUP(A6,Reg_Scores!$I:$AB,MATCH(Summary!$B$1, Reg_Scores!$I$2:$AB$2,0),0)
Where is my thought process failing?

You've defined the lookup_array as $I:$AB so the formula looks for the
lookup_value (A6) in column I not column B.

Try this:

=VLOOKUP(A6,Reg_Scores!$B:$AB,MATCH(Summary!$B$1,R eg_Scores!$B$2:$AB$2,0),0)

Biff

"The Hawk" wrote in message
...
Thanks. It fixed that problem. I then tried to modify it for another
situation and it didn't work. Following is the situation:

B C D E-H I J
AB
Name | Week -5 | Week -4 | ... | Week 1 | Week 2 | etc...

My modification was:

=VLOOKUP(A6,Reg_Scores!$I:$AB,MATCH(Summary!$B$1,R eg_Scores!$I$2:$AB$2,0),0)

Where is my thought process failing?

Thnaks...

"Biff" wrote:

Hi!

Not sure I follow you........

If you enter 0 in B1 (or, if B1 is empty) then the formula is:

=VLOOKUP(A6,Points!$B:$AB,1,FALSE)

And returns the lookup_value (if found) from the first column of the
lookup_array.

Week 0 is in the second column of the table_array so you would need to
enter
1 in B1.

I tried MATCH as so:
=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!C2:AB2,0),FALSE)

If you use Match to find the column then in B1 you'd have to enter: Week
n,
then change the range to:

=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!B2:AB2,0),0)

Biff

"The Hawk" wrote in message
...
I have a workbook that uses VLOOKUP on the Summary sheet to find values
associated with a Name and Week #. The worksheet I'm trying to capture
the
data from looks like:
B C D E
Name | Week 0 | Week 1 | Week 2 | etc

Week 0 indicates the beginning and no real values are entered. My
formula
is:
=VLOOKUP(A6,Points!$B:$AB,$B$1+1,FALSE)

The problem is the result expected for Week 0 is "0" but instead it
returns
the Name.

I tried MATCH as so:
=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!C2:AB2,0),FALSE)

Same results received. Any number other than 0 in $B$1 returns the
correct
result, but I want the initial sheet to show the corrct values.

Any help will be appreciated.

VLOOKUP Returns Erroneous Value When Control Data is Variable
 

You're welcome!

Biff

"The Hawk" wrote in message
...
Thank You!

"Biff" wrote:

B C D E-H I J
AB
Name | Week -5 | Week -4 | ... | Week 1 | Week 2 | etc...
My modification was:
=VLOOKUP(A6,Reg_Scores!$I:$AB,MATCH(Summary!$B$1, Reg_Scores!$I$2:$AB$2,0),0)
Where is my thought process failing?

You've defined the lookup_array as $I:$AB so the formula looks for the
lookup_value (A6) in column I not column B.

Try this:

=VLOOKUP(A6,Reg_Scores!$B:$AB,MATCH(Summary!$B$1,R eg_Scores!$B$2:$AB$2,0),0)

Biff

"The Hawk" wrote in message
...
Thanks. It fixed that problem. I then tried to modify it for another
situation and it didn't work. Following is the situation:

B C D E-H I J
AB
Name | Week -5 | Week -4 | ... | Week 1 | Week 2 | etc...

My modification was:

=VLOOKUP(A6,Reg_Scores!$I:$AB,MATCH(Summary!$B$1,R eg_Scores!$I$2:$AB$2,0),0)

Where is my thought process failing?

Thnaks...

"Biff" wrote:

Hi!

Not sure I follow you........

If you enter 0 in B1 (or, if B1 is empty) then the formula is:

=VLOOKUP(A6,Points!$B:$AB,1,FALSE)

And returns the lookup_value (if found) from the first column of the
lookup_array.

Week 0 is in the second column of the table_array so you would need to
enter
1 in B1.

I tried MATCH as so:
=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!C2:AB2,0),FALSE)

If you use Match to find the column then in B1 you'd have to enter:
Week
n,
then change the range to:

=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!B2:AB2,0),0)

Biff

"The Hawk" wrote in message
...
I have a workbook that uses VLOOKUP on the Summary sheet to find
values
associated with a Name and Week #. The worksheet I'm trying to
capture
the
data from looks like:
B C D E
Name | Week 0 | Week 1 | Week 2 | etc

Week 0 indicates the beginning and no real values are entered. My
formula
is:
=VLOOKUP(A6,Points!$B:$AB,$B$1+1,FALSE)

The problem is the result expected for Week 0 is "0" but instead it
returns
the Name.

I tried MATCH as so:
=VLOOKUP(A6,Points!$B:$AB,MATCH(Summary!$B$1,Point s!C2:AB2,0),FALSE)

Same results received. Any number other than 0 in $B$1 returns the
correct
result, but I want the initial sheet to show the corrct values.

Any help will be appreciated.