VLOOKUP & dynamic ranges

**tone640** · 07-20-2011, 05:48 AM

I get a #REF! error with the following formula:

=VLOOKUP($M2,INDIRECT(TEXT($AZ2,"DDD")&"1"),17,0)

M2 is a date, so the range will be called "Sun1" when the date is a Sunday. The range "Sun1" 'refers to':

=INDEX(Sun!$C:$C,1):INDEX(Sun!$CA:$CA,COUNTA(Sun!$A:$A))

When I type "Sun1" in the named range box, it highlights the correct area on a sheet called "Sun". When I evaluate the VLOOKUP, the range evaluates correctly to "Sun1".

I'm stumped - any ideas?

Thanks in advance.

**NBVC** · 07-20-2011, 07:57 AM

What is this part below supposed to result in... this is supposed to result in a range cover that will cover at least 17 columns?

INDIRECT(TEXT($AZ2,"DDD")&"1")

**tone640** · 07-20-2011, 08:39 AM

Originally Posted by NBVC

What is this part below supposed to result in... this is supposed to result in a range cover that will cover at least 17 columns?

INDIRECT(TEXT($AZ2,"DDD")&"1")

This is the equivalent of INDIRECT(Sun1) - or another day of the week - which is a range that covers columns C:CA, and is as many rows as there is data in column A.

**NBVC** · 07-20-2011, 08:49 AM

So this doesn't work?

e.g.

=VLOOKUP($M2,Sun1,17,0)

if not, then perhaps post a sample workbook

**tone640** · 07-20-2011, 08:54 AM

Originally Posted by NBVC

So this doesn't work?

e.g.

=VLOOKUP($M2,Sun1,17,0)

if not, then perhaps post a sample workbook

That is what is confusing - that works fine, the range is defined correctly, and

=TEXT($AZ2,"DDD")&"1"

evaluates to "Sun1".

**NBVC** · 07-20-2011, 08:56 AM

Maybe then it cannot be done with the INDIRECT reference... if you posted a sample workbook, it would be easier to confirm.

**tone640** · 07-20-2011, 09:33 AM

Sample attached.

**NBVC** · 07-20-2011, 09:55 AM

Yeah, I think using the formula in the Named range to reference causes the issue...

If the range selection is consistent in the daily sheets, then you can use indirect for just getting the month text...

e.g.

=VLOOKUP($M2,INDEX(INDIRECT(TEXT(AZ2,"DDD")&"!$C:$C"),1):INDEX(INDIRECT(TEXT(AZ2,"DDD")&"!$CA:$CA"),COUNTA(INDIRECT(TEXT(AZ2,"DDD")&"!$A:$A"))),17,0)

copied down... The variable is the day in AZ and the ranges will be the same...

Does that work for you?

**tone640** · 07-20-2011, 11:56 AM

Thanks NBVC, I'll try that or maybe just use a fixed range.

Any ideas why this won't work?

**NBVC** · 07-20-2011, 12:08 PM

It's an Excel fact. You can't indirectly reference a named range with a formula in it.... much the same way as with Data Validation and trying to use dynamic ranges with formulas... see first sentence here.