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

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

"The Hawk" <[email protected]> wrote in message
news:[email protected]...
>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.

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" 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,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
>
> "The Hawk" <[email protected]> wrote in message
> news:[email protected]...
> >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.
>
>
>

> 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

"The Hawk" <[email protected]> wrote in message
news:[email protected]...
> 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" 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,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
>>
>> "The Hawk" <[email protected]> wrote in message
>> news:[email protected]...
>> >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.
>>
>>
>>

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,Reg_Scores!$B$2:$AB$2,0),0)
>
> Biff
>
> "The Hawk" <[email protected]> wrote in message
> news:[email protected]...
> > 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" 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,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
> >>
> >> "The Hawk" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >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.
> >>
> >>
> >>
>
>
>

You're welcome!

Biff

"The Hawk" <[email protected]> wrote in message
news:[email protected]...
> 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,Reg_Scores!$B$2:$AB$2,0),0)
>>
>> Biff
>>
>> "The Hawk" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" 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,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
>> >>
>> >> "The Hawk" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >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.
>> >>
>> >>
>> >>
>>
>>
>>