Vlookup, Index, Match, Offset, What combination should I use?

HI everyone

I hope someone can help me here.

I have attached an example s/sheet. Basically this is an excerpt of the data that sits in a pivot table. What I want to do is from another sheet query this data. I don't want to use another pivot table as they are quite hungry in terms of memory and the data source we have is quite large.

In essence what I want to achieve is in cell G2 the user enters a code. A function (vlookup?) will then scan column A to find that code.

The function then needs to look across and sum the total of Requests and Responses for all the dates. Whilst the dates may change, the number of dates will remain the same.

Once it has summed them it needs to return the totals to cells G4 and G5. Additionally it needs to fill in the relevant total (offset?) for the corresponding week as detailed in columns H-AH.

It seems quite a simple lookup issue but I am not very versed in nested lookups. I have looked around and it seems INDEX woudl do the job but I am at a loss on how to construct this type of function.

Any help would be greatly appreciated.

Thanks and kind regards
Peter

Hi

see attached for one possible solution.

This solution assumes that the number of dates are always 27 and that the same dates appear for every code in the same order.

If the dates or their order changes for each code, the formulas won't work.

There are several different ways to skin this cat, so if you're not happy with this solution, I can have another try.

HTH

You're welcome!

And I can understand what the formula is doing which is always a bonus!

that's a big step in the right direction. I often find surprising solutions here and enjoy when the light comes up in my head.

cheers

ps. please mark the thread as solved