Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Lookup/Double Lookup
I have a spreadsheet that I use VLookup to find a Person's scores
based on Month. However, I have to change the reference number in the VLookup manually in order to get my results. I use column number 28 for April, column number 57 for May. I was using a very basic formula that got me by using IF/Vlookup for April alone, but now that it's May, I had to change the column number manually. I tried this formula, but it seems that you can't nest a Vlookup inside a Vlookup! :) =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA, 57,FALSE)),FALSE) Help would be greatly appreciated!! :) Thanks for your time & efforts. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Lookup/Double Lookup
In your second VLOOKUP, your range(array) is AZ:BA which is 2 columns. Then
your column index is 57. I think you probably want 2. =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA,2,0)),FALSE) "Ryan" wrote in message oups.com... I have a spreadsheet that I use VLookup to find a Person's scores based on Month. However, I have to change the reference number in the VLookup manually in order to get my results. I use column number 28 for April, column number 57 for May. I was using a very basic formula that got me by using IF/Vlookup for April alone, but now that it's May, I had to change the column number manually. I tried this formula, but it seems that you can't nest a Vlookup inside a Vlookup! :) =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA, 57,FALSE)),FALSE) Help would be greatly appreciated!! :) Thanks for your time & efforts. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Lookup/Double Lookup
I'm trying to understand how you get column 57.
Your range - B:BA doesn't have 57 columns. It has 52 Do you have column headers that are the names of the months? If so, you can use a MATCH function to calculate the column number for you: =VLOOKUP($A14&C$13,'2007'!$B:IV,MATCH("May",B1:IV1 ,0),0) Or, use a cell to hold the month name: A1 = May =VLOOKUP($A14&C$13,'2007'!$B:IV,MATCH(A1,B1:IV1,0) ,0) Biff "Ryan" wrote in message oups.com... I have a spreadsheet that I use VLookup to find a Person's scores based on Month. However, I have to change the reference number in the VLookup manually in order to get my results. I use column number 28 for April, column number 57 for May. I was using a very basic formula that got me by using IF/Vlookup for April alone, but now that it's May, I had to change the column number manually. I tried this formula, but it seems that you can't nest a Vlookup inside a Vlookup! :) =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA, 57,FALSE)),FALSE) Help would be greatly appreciated!! :) Thanks for your time & efforts. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Lookup/Double Lookup
The only problem with that is there aren't 57 columns in the range
'2007'!$B:$AC. Biff "PCLIVE" wrote in message ... In your second VLOOKUP, your range(array) is AZ:BA which is 2 columns. Then your column index is 57. I think you probably want 2. =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA,2,0)),FALSE) "Ryan" wrote in message oups.com... I have a spreadsheet that I use VLookup to find a Person's scores based on Month. However, I have to change the reference number in the VLookup manually in order to get my results. I use column number 28 for April, column number 57 for May. I was using a very basic formula that got me by using IF/Vlookup for April alone, but now that it's May, I had to change the column number manually. I tried this formula, but it seems that you can't nest a Vlookup inside a Vlookup! :) =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA, 57,FALSE)),FALSE) Help would be greatly appreciated!! :) Thanks for your time & efforts. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Lookup/Double Lookup
That's what I said! But maybe we're not looking at the same post. The
range in the second VLOOKUP is "AZ:BA", which is only 2 columns. Though "BA" is the 57th column, it seems as though the second column of the range ("AZ:BA") is what was desired. That's why I said "I think you probably want 2". Maybe I'm wrong though. "T. Valko" wrote in message ... The only problem with that is there aren't 57 columns in the range '2007'!$B:$AC. Biff "PCLIVE" wrote in message ... In your second VLOOKUP, your range(array) is AZ:BA which is 2 columns. Then your column index is 57. I think you probably want 2. =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA,2,0)),FALSE) "Ryan" wrote in message oups.com... I have a spreadsheet that I use VLookup to find a Person's scores based on Month. However, I have to change the reference number in the VLookup manually in order to get my results. I use column number 28 for April, column number 57 for May. I was using a very basic formula that got me by using IF/Vlookup for April alone, but now that it's May, I had to change the column number manually. I tried this formula, but it seems that you can't nest a Vlookup inside a Vlookup! :) =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA, 57,FALSE)),FALSE) Help would be greatly appreciated!! :) Thanks for your time & efforts. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Lookup/Double Lookup
So it looks like I have a few errors in my formula to begin with which
caused the issue. I appreciate the second set of eyes on my formula, and I learned that you can nest VLookup (if you have the right ranges!). Thanks for everyone's help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Lookup/Double Lookup
"BA" is the 57th column
I'm pretty sure BA is the 53rd column! Biff "PCLIVE" wrote in message ... That's what I said! But maybe we're not looking at the same post. The range in the second VLOOKUP is "AZ:BA", which is only 2 columns. Though "BA" is the 57th column, it seems as though the second column of the range ("AZ:BA") is what was desired. That's why I said "I think you probably want 2". Maybe I'm wrong though. "T. Valko" wrote in message ... The only problem with that is there aren't 57 columns in the range '2007'!$B:$AC. Biff "PCLIVE" wrote in message ... In your second VLOOKUP, your range(array) is AZ:BA which is 2 columns. Then your column index is 57. I think you probably want 2. =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA,2,0)),FALSE) "Ryan" wrote in message oups.com... I have a spreadsheet that I use VLookup to find a Person's scores based on Month. However, I have to change the reference number in the VLookup manually in order to get my results. I use column number 28 for April, column number 57 for May. I was using a very basic formula that got me by using IF/Vlookup for April alone, but now that it's May, I had to change the column number manually. I tried this formula, but it seems that you can't nest a Vlookup inside a Vlookup! :) =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA, 57,FALSE)),FALSE) Help would be greatly appreciated!! :) Thanks for your time & efforts. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Lookup/Double Lookup
My fault...You're correct! But my main focus was that the index should
neither be 53 nor 57. The range was only 2 columns wide. Thanks for pointing that out. One of those days. Regards, Paul "T. Valko" wrote in message ... "BA" is the 57th column I'm pretty sure BA is the 53rd column! Biff "PCLIVE" wrote in message ... That's what I said! But maybe we're not looking at the same post. The range in the second VLOOKUP is "AZ:BA", which is only 2 columns. Though "BA" is the 57th column, it seems as though the second column of the range ("AZ:BA") is what was desired. That's why I said "I think you probably want 2". Maybe I'm wrong though. "T. Valko" wrote in message ... The only problem with that is there aren't 57 columns in the range '2007'!$B:$AC. Biff "PCLIVE" wrote in message ... In your second VLOOKUP, your range(array) is AZ:BA which is 2 columns. Then your column index is 57. I think you probably want 2. =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA,2,0)),FALSE) "Ryan" wrote in message oups.com... I have a spreadsheet that I use VLookup to find a Person's scores based on Month. However, I have to change the reference number in the VLookup manually in order to get my results. I use column number 28 for April, column number 57 for May. I was using a very basic formula that got me by using IF/Vlookup for April alone, but now that it's May, I had to change the column number manually. I tried this formula, but it seems that you can't nest a Vlookup inside a Vlookup! :) =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA, 57,FALSE)),FALSE) Help would be greatly appreciated!! :) Thanks for your time & efforts. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Variable Lookup/Double Lookup
Yeah, I kind of knew what the OP was trying to do but I suggested using
MATCH instead. One of those days. Been there many a time! Cheers! Biff "PCLIVE" wrote in message ... My fault...You're correct! But my main focus was that the index should neither be 53 nor 57. The range was only 2 columns wide. Thanks for pointing that out. One of those days. Regards, Paul "T. Valko" wrote in message ... "BA" is the 57th column I'm pretty sure BA is the 53rd column! Biff "PCLIVE" wrote in message ... That's what I said! But maybe we're not looking at the same post. The range in the second VLOOKUP is "AZ:BA", which is only 2 columns. Though "BA" is the 57th column, it seems as though the second column of the range ("AZ:BA") is what was desired. That's why I said "I think you probably want 2". Maybe I'm wrong though. "T. Valko" wrote in message ... The only problem with that is there aren't 57 columns in the range '2007'!$B:$AC. Biff "PCLIVE" wrote in message ... In your second VLOOKUP, your range(array) is AZ:BA which is 2 columns. Then your column index is 57. I think you probably want 2. =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA,2,0)),FALSE) "Ryan" wrote in message oups.com... I have a spreadsheet that I use VLookup to find a Person's scores based on Month. However, I have to change the reference number in the VLookup manually in order to get my results. I use column number 28 for April, column number 57 for May. I was using a very basic formula that got me by using IF/Vlookup for April alone, but now that it's May, I had to change the column number manually. I tried this formula, but it seems that you can't nest a Vlookup inside a Vlookup! :) =VLOOKUP(CONCATENATE($A14,C$13),'2007'!$B:$AC,(VLO OKUP(E2,AZ:BA, 57,FALSE)),FALSE) Help would be greatly appreciated!! :) Thanks for your time & efforts. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Double LOOKUP? Help please... | Excel Worksheet Functions | |||
double lookup | Excel Worksheet Functions | |||
Double lookup | Excel Worksheet Functions | |||
More help need with my double col lookup function | Excel Worksheet Functions | |||
Double entry lookup | Excel Worksheet Functions |