Vlookup in a named range

[tuph]

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

 

[Alan]

=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 F1500 as 'MyRange' it should look
either like

=VLOOKUP(B35,MyRange,10,FALSE)
or
=VLOOKUP(B35,F1500,10,FALSE)

also, you cant have spaces in a named range,
Regards,
Alan.

 

[Guest]

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

 

[Peo Sjoblom]

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

 

[Peo Sjoblom]

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

 

[tuph]

Thanks for trying, but no luck so far. The range is in anothe
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 look
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$10

 

[Ron Coderre]

Tuph

I think I might understand the problem. Your named range refers to tw
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,
Ro