Lookup automatically specified arrays

[exoticdisease]

I'd like my array to be specified based on a value elsewhere in the
spreadsheet.

e.g. I have data separated by days and months...I would like the array to be
the whole of one month's worth, so for July it might be d2:ah93 worth of data.

Normally you'd write something like =hlookup(b3,d2:ah93,5,)

However, the data to look up in b3 is a single number date e.g. 25 for 25th
of the month. I want the array to automatically search for the position in
the sheet that has the heading "July" for example, then lookup from there.
When the month changes, I want to be able to type august into my "date" box
and it return the array that starts august, so maybe ai2:bh93, instead of the
above.

Sorry this is long and complicated, any clarifications, please ask and I'll
try and help.

Thanks
Rob

 

[Spiky]

I'd like my array to be specified based on a value elsewhere in the
spreadsheet.

e.g. I have data separated by days and months...I would like the array to be
the whole of one month's worth, so for July it might be d2:ah93 worth of data.

Normally you'd write something like =hlookup(b3,d2:ah93,5,)

However, the data to look up in b3 is a single number date e.g. 25 for 25th
of the month. I want the array to automatically search for the position in
the sheet that has the heading "July" for example, then lookup from there.
When the month changes, I want to be able to type august into my "date" box
and it return the array that starts august, so maybe ai2:bh93, instead of the
above.

Sorry this is long and complicated, any clarifications, please ask and I'll
try and help.

Thanks
Rob

If July's area doesn't change, you can just name that range (say,
"July"), then use the name in your formula like this: =hlookup(b3,July,
5,)

Or, a reference to that name, which I think you called your "date"
box, but you will need Indirect to do so:
=hlookup(b3,INDIRECT(ref),5,)