Using a Named Range to apply to a Lookup Formula

[kiwiorn]

I want to create as short a formula as possible using a lookup to determine which named range to look up depending on the circumstance.

ie. Budget, Actual, Trended as examples of 3 similar types of information for a given year. Each one has been named as a range. I want to provide an option of drivers (say, Budget, Actual, Trended) so that the user can select which driver is most appropriate, and the formula looks up which named range to use and then returns the correct monthly value.

There are actually many more than 3 options hence the need to use a lookup table to determine which named range to look up.

Example: The following part of the formula returns the correct named range, but it fails to look up the data within that named range.

=VLOOKUP($B6,VLOOKUP($R6,DriverRange,2,FALSE),E$3+2,FALSE)
where B6 provides a unique code reference, and the formula VLOOKUP($R6,DriverRange,2,FALSE) correctly provides "RollBud" which is a named range. However, the formula appears not to look up the named range. Does anyone know why not, and how I can correct this?

 

[Greg Truby]

You need to use INDIRECT(). If I've followed the bouncing ball correctly, you need sumpin like:<ul>[*]=VLOOKUP($B6,indirect(VLOOKUP($R6,DriverRange,2,FALSE)),E$3+2,FALSE) [/list]

 

[kiwiorn]

Thanks Greg. Works a treat.