Use Text and Cell vale to reference a named range inside a formula

[Abdul]

I have named range in my workbook like AB_Jan10, AB_Feb10 .. etc

and in one sheet I have Jan, Feb, Mar etc as cell values

I want to use a formula something like =VLOOKUP($B4,("AB_" &
TEXT(D3,"mmm")&"10"),3,FALSE) assuming D3 contains a date say 31-
Jan-10 and my named range = AB_Jan10 and want to get the value from
third column in the named range.

Is there a way to get a working formula in Excel

Thanks

 

[Ron Rosenfeld]

I have named range in my workbook like AB_Jan10, AB_Feb10 .. etc

and in one sheet I have Jan, Feb, Mar etc as cell values

I want to use a formula something like =VLOOKUP($B4,("AB_" &
TEXT(D3,"mmm")&"10"),3,FALSE) assuming D3 contains a date say 31-
Jan-10 and my named range = AB_Jan10 and want to get the value from
third column in the named range.

Is there a way to get a working formula in Excel

Thanks

=VLOOKUP($B4,INDIRECT("AB_" & TEXT(D3,"mmm")&"10"),3,FALSE)

 

[Abdul]

=VLOOKUP($B4,INDIRECT("AB_" & TEXT(D3,"mmm")&"10"),3,FALSE)

Gives me #REF! Error. My range is correct (dynamic) which has five
columns and I am using the same named range in other formulas
(straight forward) which works fine

 

[Ron Rosenfeld]

Gives me #REF! Error. My range is correct (dynamic) which has five
columns and I am using the same named range in other formulas
(straight forward) which works fine

Well, this is the first you mentioned that the range is dynamic.

With regard to NAME's, they have to be defined as a *reference* to be
an acceptable argument to INDIRECT. But a dynamic name is defined as
a *formula*, and not a reference, so INDIRECT will not work.

If there is some spatial relationship between your tables, you could
probably devise a dynamic formula that would point to the correct
table depending on the value in Dn. But we'd need to know more about
the setup of your worksheet with the tables in order to come up with
something.

 

[Ron Rosenfeld]

Gives me #REF! Error. My range is correct (dynamic) which has five
columns and I am using the same named range in other formulas
(straight forward) which works fine

Here is an example.

Let us assume that your various tables are on a worksheet named
"Tables".

And, since you wrote they are all five columns, let us assume that the
first table (AB_JAN10) starts in A1; the second (AB_FEB10) in F1, and
so forth.

That being the case, this formula should work:

=VLOOKUP($B$4,OFFSET(Tables!$A$1,0,(MONTH(D3)-1)*5,
COUNTA(OFFSET(Tables!A1,0,(MONTH(D3)-1)*5,1000,1)),5),3,FALSE)

The "1000" just needs to be a value larger than the largest possible
number of rows in the table (and not greater than the maximum number
of rows in the worksheet)

If you do not need a dynamic named range, the formula can be
simplified:

=VLOOKUP($B$4,OFFSET(Tables!$A$1,0,(MONTH(D3)-1)*5,1000,5),3,FALSE)