Variable Lookup/Double Lookup

[Ryan]

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,(VLOOKUP(E2,AZ:BA,
57,FALSE)),FALSE)

Help would be greatly appreciated!! Thanks for your time & efforts.

 

[PCLIVE]

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,(VLOOKUP(E2,AZ:BA,2,0)),FALSE)

 

[T. Valko]

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

 

[T. Valko]

The only problem with that is there aren't 57 columns in the range
'2007'!$B:$AC.

Biff

 

[PCLIVE]

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.

 

[Ryan]

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.

 

[T. Valko]

"BA" is the 57th column

I'm pretty sure BA is the 53rd column!

Biff

 

[PCLIVE]

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]

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