Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with hlookup
Howdy All,
I'm having a problem with the hlookup function. I've used it before successfully, but never before when the lookup_value in the array is in a merged cell. I have on one sheet a list of months. Each month is merged from 4 cells into one and has 4 columns underneath it: the three leftmost columns are data, and the 4th column is the total of the previous three columns. So far, there are about 24 months. What I want to do is have another sheet that lists each month, but has only the total column underneath it. My problem is occuring when I paste the formula over because the data that I want is occurs every 4 columns, so I cant just past over. Please let me know if this isn't clear enough. Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
help with hlookup
Hi,
Instead of Hlookup try a combination of OFFSET and INDEX, something like this: =OFFSET(INDEX($B$4:$L$9,MATCH($C13,$A$5:$A$8,0),MA TCH(D$12,$B$3:$M$3,0)),0,3) where $B$4:$L$9 is your data, $C13 is a product, $A$5:$A$8 is product list, D$12 is a month to lookup and $B$3:$M$3 is your month range of merged cells, the formula will offset the value returned by the index function by 3 columns (sum column) Hope this helps! Jean-Guy "Cam1234" wrote: Howdy All, I'm having a problem with the hlookup function. I've used it before successfully, but never before when the lookup_value in the array is in a merged cell. I have on one sheet a list of months. Each month is merged from 4 cells into one and has 4 columns underneath it: the three leftmost columns are data, and the 4th column is the total of the previous three columns. So far, there are about 24 months. What I want to do is have another sheet that lists each month, but has only the total column underneath it. My problem is occuring when I paste the formula over because the data that I want is occurs every 4 columns, so I cant just past over. Please let me know if this isn't clear enough. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HLOOKUP | Excel Discussion (Misc queries) | |||
Hlookup for row 3 and 4 | Excel Worksheet Functions | |||
hlookup | Excel Discussion (Misc queries) | |||
hlookup & ? | Excel Worksheet Functions | |||
HLookup ??? | Excel Discussion (Misc queries) |