Named Range in VLOOKUP #VALUE's

[bobsaved]

Thanks to all for your work on this site - learned a lot here in a short time.

I am stumped by what looks so simple…

I have a formula =vlookup(index, namedrange column, false)
In one case, with the namedrange written in the formula no problem, returns correct value.
If I put the named range in another cell, and point to that cell, it returns an error.

I've tried everything, looked everywhere - no luck. Any help greatly appreciated!

 

[etaf]

If I put the named range in another cell, and point to that cell, it returns an error.

are you using indirect to return the value in the cell and use in the formula?

for example a Vlookup() formula may look like this
=VLOOKUP(E3,E1:G14,3,FALSE)
and if we put into Cell O2 the value E1:G14
then use indirect to use the contents of the cell
we change to
=VLOOKUP(E3,INDIRECT(O3),3,FALSE)

 

[bobsaved]

Wayne,

Thank you for your reply. I should have been more specific. I am trying to get rid of a ton of indirect() functions by using the named range in the formula directly. I used indirect() and cloned it to thousands of formulas. This seems to slow up processing significantly. I read that indirect is "volatile" and to be avoided if possible. My application is not conducive to an index match approach (as an alternative).

When I type the named range into the formula it works fine. Why can't we just point to the cell that creates the named range locally (using concatenated text strings that isolate the named range among many named ranges)? In your example, why can't you point to cell O2 directly, without the indirect reference? Like what if your cell O2 was a cell that created the named range from its parts?

Thanks again,
Bob

 

[etaf]

i dont know of a way.
the indirect() function , takes the contents of the cell and allows it to be used in the formula
Pointing to a cell, will be seen by the formula as a part of the formula so a reference to cell O2 is to use O2 as the part of the formula and not use the contents

but I'm not as advanced as some of the other members here, who may offer a solution