Variable Lookup/Double Lookup

  • Thread starter Thread starter Ryan
  • Start date Start date
R

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.
 
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)
 
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
 
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.
 
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.
 
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
 
Back
Top