vlookup, range name is in a cell, not directly in the formula

[JJ]

I have set up a vlookup like this:

=VLOOKUP($C11,DGPlus,H$2,FALSE) (which works)​

As per best practice the range that is being looked up is in a named range called "DGPlus".
(and the lookup value is in column $C, and the lookup column number is in row $2)
Now I want to have a series of vlookups in this model some looking at a range of databases.
So to do that I would put the named range for the database "DGPlus" in column $D. And have a formula like this:

=VLOOKUP($C11,$D11,H$2,FALSE)​

(and in other cells I would put a different named range in column $D)​

Unfortunately Excel is not cooperating, and when I do this I get:

#N/A​

I have tried all sorts of things to try to get Excel to understand what is in column $D. Such as formatting it as text, etc. But I am having no joy.
Please help.
Or even knowing that it is not possible would be helpful.

​

 

[sgmpatnaik]

@jj

Please Try this

=VLOOKUP(look-value,INDIRECT(CELL),col,match-type)

=VLOOKUP($C11,INDIRECT($D11),H$2,FALSE)

Hope your problem solve other please inform

Thanks

Patnaik

 

[SirJB7]

Hi, JJ!
Take care that VLOOKUP doesn't work with INDIRECT and named ranges, if these ranges are dynamic, it works just with fixed ranges.
Regards!

 

[JJ]

Thank you Patnaik, that worked. I will add it to my list of tricks in personal.xls.
SirJB7, luckily I am only using fixed ranges in this model, whew.
With this help you have given me I will end up with a much better model, and the sources of all the numbers will be a lot clearer, which will help from an auditing point of view.

 

[SirJB7]

Hi, JJ!
Remember than unless part of the audit division team, i.e. being nice and normal and not hated people, one never should do anything -but the opposite!- to help auditing points of view... that they work and squeeze their brains trying to figure what we inadvertently obfuscated slightly
Regards!

 

[NARAYANK991]

Hi ,

There's a lot of information on the problem of using INDIRECT with dynamic named ranges here :

http://dailydoseofexcel.com/archives/2005/03/01/indirect-and-dynamic-ranges/

Narayan