HLOOKUP using a named range returns #VALUE!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
MicheleO said:
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
Click to expand...


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...


--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy
 
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
Click to expand...


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
__________________________
 
Back
Top