Pull data from a table to matching Vertical and Horizontal Criteria

[sanantonio]

Hi All,

Normally I would be able to manipulate the data prior to extraction to make it "pivotable" but alas we have one KPI measured by an antiquated system that does not allow for editing prior to export so I get that looks like this:

What I need to be able to do is have a formula that will return a value between D2 to Q9999 when it matches 3 criteria: Item Nr, DC (The Verticals) and Week number (The Horizontal).

I've tried some Index/Match's but can't get it to work with the mixture of vertical and horizontal.

Open to any solution, even if someone had a loop macro that would convert the data into a pivotable format...
(This is an example I put together manually, but this manual creation isn't feasible because I have 600+ items)

Thanks in advance!

 

[Fluff]

How about

=SUMPRODUCT((B2:B1000=X9)*(C2:C1000=Y9)*(D1:Q1=Z9)*(D2:Q1000))

 

[sanantonio]

You sir! Have saved my ****.

Thank you for the quick response!

 

[alansidman]

Load your data to Power Query (Get and Transform Data on the Data Tab). In the PQ editor, click on Transform. Highlight columns B and C. Select Unpivot and Unpivot other columns. On the home tab close and load to a table. Select location.

 

[Fluff]

You're welcome & thanks for the feedback.