# Named Range in VLOOKUP #VALUE's

#### bobsaved

##### New Member
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!

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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)

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

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

Replies
5
Views
519
Replies
8
Views
432
Replies
3
Views
88
Replies
3
Views
103
Replies
9
Views
152

1,206,921
Messages
6,075,578
Members
446,147
Latest member
homedecortips

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back