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