HLOOKUP using a named range returns #VALUE!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MicheleO - 24 Aug 2006 14:48 GMT

Excel 2003 v.11
=HLOOKUP(W2,WholeYear,13) = #VALUE!
Where WholeYear is a named range Jan:Dec!$L$2:$AP$17, that spans 12
worksheets. The first row in the range (Row 2) contains the 365 days of the
year, in order.
Everything about this looks correct, and it works if I refer to a signle
sheet, but I am getting a Value error when searching the whole year.
Can anyone help? I'm really stuck.
Thanks

Reply to this Message

Franz Verga - 24 Aug 2006 17:30 GMT

> Excel 2003 v.11
> =HLOOKUP(W2,WholeYear,13) = #VALUE!
[quoted text clipped - 6 lines]
> Can anyone help? I'm really stuck.
> Thanks

Hi Michele,

It's very simple... The HLOOKUP and VLOOKUP functions cannot work on 3D
references, i.e. ranges that span over more than one worksheet...

Signature

Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Reply to this Message

Richard Buttrey - 24 Aug 2006 18:06 GMT

>Excel 2003 v.11
>=HLOOKUP(W2,WholeYear,13) = #VALUE!
[quoted text clipped - 5 lines]
>Can anyone help? I'm really stuck.
>Thanks

AFAIAA lookups refer to single sheet ranges only.
The help mentions 'table_array' not a 'cube array' which I guess you'd
need for a multi sheet lookup.

If there were such a thing, let's imagine it called a DHLookup for
Dimension Horizontal lookup, you'd also need another parameter to
identify the sheet offset.

You could probably build a UDF to achieve the same sort of
functionality and pass the sheet number as an offset from the first
Jan! sheet

If I get some spare time I'll have a go.

HTH
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

Reply to this Message