Vlookup within a vlookup function

[James Lee]

Ladies & Gentlemen,

I am creating a vlookup function where the name is
referring to another name. The formula does not recognise
this reference.

For example,

vlookup(A1,act01,2) is fine as 'act01' is referring to the
named range. However, I need to reference it so that the
named range can vary accordingly to another variable like

Jan 04 named range is act01 and
Feb 04 named range is act02 etc...

So where I create the function of

Vlookup(A1,vlookup(AA1,AB1:AC12,2),2), it does not work
anymore as the vlookup function embeded within another
vlookup function does not recognise the named range in the
second function.

Please HELP,

THANK YOU VERY MUCH!

 

[David McRitchie]

Hi James,
Look for INDEX / MATCH in
http://www.mvps.org/dmcritchie/excel/vlookup.htm#match


HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

 

[Arvi Laanemets]

Hi

The second parameter in VLOOKUP must be a cell range. A VLOOKUP function
never returns a range - it returns a value. You can get a range using OFFSET
or INDEX instead. Or you don't need it at all? What is your table structure
at all? When you have p.e. in column D the values you are searching for, in
cell AA1 the month in some valid date format, and in columns E values for
January, February, etc, then you can refer in your VLOOKP function to whole
datarange, and have 3rd parameter change according to month. Something like:
=VLOOKUP(A1,$D$1,$P$100,MONTH(AA1)+1)
or
=VLOOKUP(A1,$D$1,$P$100,MONTH(AA1)+1,FALSE) when you need exact match for
lookup.

 

[Aladin Akyurek]

=VLOOKUP(A1,INDIREC(B1),2)

where B1 houses a name that refers to a lookup table like act01, act02, etc.

 

[Frank Kabel]

Hi
Aladin probably meant
=VLOOKUP(A1,INDIRECT(B1),2)

 

[James Lee]

Aladin, David & Arvi,

THANK YOU FOR YOUR KIND ADVICE!

I tried and it does not work.

May you allow me to illustrate the table structure for you
to better understand it?

This is the worksheet for a 3 months rolling sales report
that I am working.

A1 = is the global cell reference that I need to change
every month. I start with Jan-04

The sales report is in range a3:d4 as follows
2 A B C D
3 Jan-04 Feb-04 Mar-04
4 Sales 100 110 131

Cell B3 reference to A1 and it will change accordingly, C3
& C4 is reference to a formula with an increment of 1
month dervied from B3.

The Sales data contained in B44 is drawn from a separate
file with named range as follows in G2:H5
1 G H
2 Jan-04 Act01
3 Feb-04 Act02
4 Mar-04 Act03
5 Apr-04 Act04

By creating a vlookup function in B44 with direct
reference is not a problem. What I need is an indirect
reference of the vlookup to reference each cell in B33
to the table in G2:H5, so that when I change the global
cell in A1 to say Feb-04, the 3 months sales data will be
updated with Feb-04 to Apr-04 sales data in B4 to D4,
instead of Jan-04 to Mar-04.

I wish that this is clear to you.

Once again, thank you!

James Lee

 

[Frank Kabel]

Hi
not quite sure but try the following in B4:
VLOOKUP(B3,$G$1:$H$20,2,0)
and copy to the right

Assumptions:
- B3 contains a date
- column G contains dates which match to B3

 

[Arvi Laanemets]

Hi

When you named ranges would be in same workbook, then you could use INDIRECT
function to create a reference - like this:
B4=VLOOKUP(B2,INDIRECT(VLOOKUP(B2,$G$2:$H$5,2,FALSE)),2)
I'm not sure about references to named range in another workbook at all, and
INDIRECT will work only, when other workbook is opened anyway. So it'll not
be comfortable to work with.

What is the data structure in this another workbook. When all data there
are on single sheet, then maybe you can do with links and worksheet
functions.

And what sort of data do you need from these named ranges. In your examples
you look for single value with Jan-04 in leftmost column of range Act01, and
want to return the value next to right of it. Somehow I doen't see any point
in it!