Using vlookup on merged cells - excel file attached

[jac89]

I'll try to explain this the best I can here, but basically I'm looking to pull data from specific rows/columns based on a lookup value that is in a merged cell. I will give a very simple example, but will attach the exact spreadsheet for more detail on what exactly I am trying to do.

Bullet points:

- There are 5 sectors listed vertically in cells A4-A8
- C1 through Q1 is a merged cell containing a date range (new merged date cell every four cells)
- further more, there is an additional merged cell covering C2 through E2 which contains a value category (weight), new merged category cell every 2 cells
- Then there is cell D3 which contains a further classification of the value type (specific benchmark)
- then cell D4 contains the value

Thus, I am trying to pull the number that satisfies the following criteria:

- Sector = A4
- Date = merged cell of C1 to Q1
- Category = merged cell of C2 to E2
- Benchmark = D3

Thus, the exact cell containing the data I need in the first date range is in D4

I understand this may be confusing, but if you take a look at the attached spreadsheet, I a simply trying to pull the weight % of the benchmark for each sector listed vertically on each of the dates. The merged cell concept is really throwing me off. (Note I only copied the first two sectors as the other sectors will use the same concept

Thank you in advance for all the help!

Andrew