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.

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.

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.

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.

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.

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.

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.

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.

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.