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

[TechKnowledge]

Are you sure this is the right file? It doesn't match up with what you stated. This file has consumer discretionary in A4, Name in B1, Date in C2:Q2, B3 and B4 are blank and B2:C2 are not merged and contain name and weight%.

You may want to un-merge cells, as they are the devil, and you will probably want an Hlookup based on your layout, but it's hard to tell with nothing matching what you are saying!

[JohnTopley]

Your description does not match your file: in row date is C1 to Q1, Value category is C2 to E2, and B3 is blank.

And where is the output meant to go? confused

[jac89]

My apologies. The cells have been edited in the original post to correspond to the attachment.

Thanks so much for your time!

[jac89]

OUTPUT (on separate tab) Cells A2 and A3 would be the two sectors. Then cells B1 through say X1 would be each individual date. Then Cells B2 through X2 and B3 through X3 would pulling the weight value for its corresponding date and sector from the attached sheet.

Hope this helps!