Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup automatically specified arrays
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup automatically specified arrays
On Jul 28, 6:47 am, exoticdisease
wrote: 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,) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using text to lookup different table arrays in vlookup. | Excel Worksheet Functions | |||
LOOKUP across multiple arrays | Excel Discussion (Misc queries) | |||
Index, Lookup and Vectors/Arrays | Excel Worksheet Functions | |||
how to automatically populate cells using lookup tables | Excel Discussion (Misc queries) | |||
3D Arrays | Excel Worksheet Functions |