help with hlookup

[Cam1234]

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!

 

[pinmaster]

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),MATCH(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