| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Aling multiple sets of data by header column | Excel Discussion (Misc queries) | |||
| Concatenate columns with cell data containing Carriage Returns | Excel Worksheet Functions | |||
| Vlookup for data contained in a cell | Excel Worksheet Functions | |||
| Variable control tip text | Excel Discussion (Misc queries) | |||
| Control Box Data Question | New Users to Excel | |||
Linear Mode
