VLOOKUP Returns Erroneous Value When Control Data is Variable

[Guest]

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,Points!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.

 

[Biff]

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,Points!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,Points!B2:AB2,0),0)

Biff

 

[Guest]

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,Reg_Scores!$I$2:$AB$2,0),0)

Where is my thought process failing?

Thnaks...

 

[Biff]

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,Reg_Scores!$B$2:$AB$2,0),0)

Biff

 

[Guest]

Thank You!

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,Reg_Scores!$B$2:$AB$2,0),0)

Biff

 

[Biff]

You're welcome!

Biff

Thank You!