Can I use vlookup to find data in a named range? I currently have a
list of locations in col A and monthly sales data in cols AP:BB.
(There's a heap of other data in the intervening cols.) I have
included cols A and AP:BB in a range I have named "Sales_FY06".

When I try to build a vlookup formula using the wizard and typing the
range name into the table array field it displays #VALUE against that
field.

My formula looks like this:

=VLOOKUP(B35,'KPIs REPORTS MASTER - FORMULA
TESTING.XLS'!Sales_FY06,10,FALSE)

Thanks in anticipation,

Trish


--
tuph
------------------------------------------------------------------------
tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
View this thread: http://www.excelforum.com/showthread...hreadid=544929

=VLOOKUP(B35,'KPIs REPORTS MASTER - FORMULA
TESTING.XLS'!Sales_FY06,10,FALSE)

What you have there doesnt look like a named range or a reference to a range
of cells, if you had named the range F1:P500 as 'MyRange' it should look
either like

=VLOOKUP(B35,MyRange,10,FALSE)
or
=VLOOKUP(B35,F1:P500,10,FALSE)

also, you cant have spaces in a named range,
Regards,
Alan.
"tuph" wrote in message
...

Can I use vlookup to find data in a named range? I currently have a
list of locations in col A and monthly sales data in cols AP:BB.
(There's a heap of other data in the intervening cols.) I have
included cols A and AP:BB in a range I have named "Sales_FY06".

When I try to build a vlookup formula using the wizard and typing the
range name into the table array field it displays #VALUE against that
field.

My formula looks like this:

=VLOOKUP(B35,'KPIs REPORTS MASTER - FORMULA
TESTING.XLS'!Sales_FY06,10,FALSE)

Thanks in anticipation,

Trish


--
tuph
------------------------------------------------------------------------
tuph's Profile:
http://www.excelforum.com/member.php...o&userid=31390
View this thread: http://www.excelforum.com/showthread...hreadid=544929

If you are referencing another workbook in your VLOOKUP formula, I believe
that workbook needs to be open.

Try opening the other workbook....Does the formula work now?

***********
Regards,
Ron

XL2002, WinXP


"tuph" wrote:


Can I use vlookup to find data in a named range? I currently have a
list of locations in col A and monthly sales data in cols AP:BB.
(There's a heap of other data in the intervening cols.) I have
included cols A and AP:BB in a range I have named "Sales_FY06".

When I try to build a vlookup formula using the wizard and typing the
range name into the table array field it displays #VALUE against that
field.

My formula looks like this:

=VLOOKUP(B35,'KPIs REPORTS MASTER - FORMULA
TESTING.XLS'!Sales_FY06,10,FALSE)

Thanks in anticipation,

Trish


--
tuph
------------------------------------------------------------------------
tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
View this thread: http://www.excelforum.com/showthread...hreadid=544929

Use the mouse and select the ranges etc and excel will give you correct
sheet name correct workbook name and correct named range, have both
workbooks open while doing so.

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"tuph" wrote in message
...

Can I use vlookup to find data in a named range? I currently have a
list of locations in col A and monthly sales data in cols AP:BB.
(There's a heap of other data in the intervening cols.) I have
included cols A and AP:BB in a range I have named "Sales_FY06".

When I try to build a vlookup formula using the wizard and typing the
range name into the table array field it displays #VALUE against that
field.

My formula looks like this:

=VLOOKUP(B35,'KPIs REPORTS MASTER - FORMULA
TESTING.XLS'!Sales_FY06,10,FALSE)

Thanks in anticipation,

Trish


--
tuph
------------------------------------------------------------------------
tuph's Profile:
http://www.excelforum.com/member.php...o&userid=31390
View this thread: http://www.excelforum.com/showthread...hreadid=544929

In this case it's better (or at least easier since one would not get any
spelling errors/misplace or forgotten apostrophes) to use the mouse to
select with and both workbooks open but if you meant that vlookup always
need the other workbook to be open it is not true

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


"Ron Coderre" wrote in message
...
If you are referencing another workbook in your VLOOKUP formula, I believe
that workbook needs to be open.

Try opening the other workbook....Does the formula work now?

***********
Regards,
Ron

XL2002, WinXP


"tuph" wrote:


Can I use vlookup to find data in a named range? I currently have a
list of locations in col A and monthly sales data in cols AP:BB.
(There's a heap of other data in the intervening cols.) I have
included cols A and AP:BB in a range I have named "Sales_FY06".

When I try to build a vlookup formula using the wizard and typing the
range name into the table array field it displays #VALUE against that
field.

My formula looks like this:

=VLOOKUP(B35,'KPIs REPORTS MASTER - FORMULA
TESTING.XLS'!Sales_FY06,10,FALSE)

Thanks in anticipation,

Trish


--
tuph
------------------------------------------------------------------------
tuph's Profile:
http://www.excelforum.com/member.php...o&userid=31390
View this thread:
http://www.excelforum.com/showthread...hreadid=544929

Thanks for trying, but no luck so far. The range is in another
worksheet in the same workbook, so both are always open.

If I select the range with the mouse, I still receive the same error.

I have also tried typing in the range name so that my formula looks
like this:
=VLOOKUP(B35,Sales_FY06,10,FALSE)
but it still doesn't work.

My named range parameters look like this:
='Supp Data'!$A$5:$A$102,'Supp Data'!$AP$5:$BB$102


--
tuph
------------------------------------------------------------------------
tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
View this thread: http://www.excelforum.com/showthread...hreadid=544929

Tuph

I think I might understand the problem. Your named range refers to two
discontigous ranges.
'Supp Data'!$A$5:$A$102
and 'Supp Data'!$AP$5:$BB$102

The lookup range argument in the VLOOKUP function requires one range.
It isn't fooled by one range name that refers to 2 ranges.

Try redefining the Sales_FY06 range so it refers to:
='Supp Data'!$A$5:$BB$102

Then adjust your VLOOKUP formula accordingly.

Something like:
=VLOOKUP(B35,Sales_FY06,52,FALSE)

Does that help?

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=544929

Thanks, Ron. I understand what you're saying, but I had hoped there
would be another way, because I can't move my data around to suit the
lookup.

My original problem is this - I have a very large workbook with several
worksheets where the location in col A is used in a vlookup to return
sales, budget, staffing, etc., figures (all of which run across the
page to column BL) in a single supplementary data worksheet in the same
workbook. There are so many other workbooks (around 60, many with 10
worksheets in them) dependent on this one base worksheet that it would
be impossible to move the data around.

At present we use vlookups which address the entire worksheet range but
every month we have to change the column number in each vlookup so that
the results match the correct month in each given data set.

I am looking for a formula that will take the location, month and data
set name (i.e. - sales, budget, or staff) from the result worksheet and
find the correct month, row and column in the matching data set in the
data worksheet.

I don't even know if this is possible - I'm exploring options. I have
one formula that does a two-way lookup for location and month, but it
only returns the first column found that matches the month.

Any ideas would be greatly appreciated.:)


--
tuph
------------------------------------------------------------------------
tuph's Profile: http://www.excelforum.com/member.php...o&userid=31390
View this thread: http://www.excelforum.com/showthread...hreadid=544929