Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,311
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Double LOOKUP? Help please... mitchy Excel Worksheet Functions 2 March 20th 06 04:05 PM
double lookup Geir Excel Worksheet Functions 0 November 2nd 05 05:47 PM
Double lookup Gary T Excel Worksheet Functions 3 July 28th 05 12:33 PM
More help need with my double col lookup function KimberlyC Excel Worksheet Functions 10 April 14th 05 12:26 AM
Double entry lookup Al Eaton Excel Worksheet Functions 2 December 13th 04 04:25 AM


All times are GMT +1. The time now is 05:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"