Vlookup - Using a named ranged for col_index_num

  • Thread starter Thread starter rocket_rob
  • Start date Start date
R

rocket_rob

I have a summary sheet I want to lookup data from a detail
sheet only for the appropriate month.

The formula is =VLOOKUP(D9,detail,12) where D9 is the
employee number, detail is the named range for the table
array, and 12 is the 12th column which is November.

I want to automate updating of this sheet, so instead
of "12" I want to use "November", which is a named range.
I want the "November" range to be driven by a Month input
column so what I really want in the formula instead
of "12" is =TEXT(A3,"mmmm") where cell A3 is 11/30/03.

I have a similar issue using =SUMIF where my sum range
of "November" works fine, however, I also cannot use a
reference of =TEXT(A3,"mmmm") in lieu of using the range
name.

Is it possible to have Excel do this so by just entering
the month in cell A3 all my formulas will update? Thanks.
 
I don't understand, do you mean that you want to replace detail with
November?
If you only want a number then =MONTH(A3) would return 11
The 12 part is the index number where vlookup will pick up the values it
will return
 
What I want to do is replace the "12" with a named range
so instead of saying "go to column 12" for the column
lookup I want to say "go to the range named November".

Further, I want my formula to be something like this:

vlookup(D9,detail,TEXT(A3,"mmmm")) where A3 is the current
month I am analyzing, and the result of TEXT(A3,"mmmm")
is "november" which is a named range in the sheet. Then,
when updating A3 each month my vlookup formula will
automatically update as well. So entering "12/31/03" in
A3 will update my formula to vlookup(D9,detail,december)

In a nutshell, can a named range be used in the col_index
portion of a vlookup function and, if so, can a formula
[TEXT(A3,"mmmm")] be used to generate that named range!

Thanks.
 
You can't if you use Vlookup, I'm afraid it is pretty clear only thing would
be if the
columns were in order and November is the 12th column then you can use

=VLOOKUP(D9,detail,MONTH(A3)+1)

for Dec the above would return 13, for Jul 8 etc..

There are other ways using index and offset that you could use to make it
more flexible
--

Regards,

Peo Sjoblom


What I want to do is replace the "12" with a named range
so instead of saying "go to column 12" for the column
lookup I want to say "go to the range named November".

Further, I want my formula to be something like this:

vlookup(D9,detail,TEXT(A3,"mmmm")) where A3 is the current
month I am analyzing, and the result of TEXT(A3,"mmmm")
is "november" which is a named range in the sheet. Then,
when updating A3 each month my vlookup formula will
automatically update as well. So entering "12/31/03" in
A3 will update my formula to vlookup(D9,detail,december)

In a nutshell, can a named range be used in the col_index
portion of a vlookup function and, if so, can a formula
[TEXT(A3,"mmmm")] be used to generate that named range!

Thanks.
-----Original Message-----
I don't understand, do you mean that you want to replace detail with
November?
If you only want a number then =MONTH(A3) would return 11
The 12 part is the index number where vlookup will pick up the values it
will return

--

Regards,

Peo Sjoblom


Thanks.


.
Click to expand...
Click to expand...
 
Back
Top