PowerPivot with Match/index or Vlookup/Hlookup

[Kalle123]

Hi!

I have a problem. I have a powerpivot. In one table (Sale) I have four columns (Item name (A), Item group (B), Weeks left to sell the item (C), Capacity (D)). In another table (Target) I have my targets and there are several columns (Item group1, 15, 14 ,13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0). The number shows what the sale target is when there a X weeks left to sell. Sale.Item group is related to Target.Item group1

I want the PowerPivot to check the row where it can find the same Item Group and then the corrosponding column for weeks left to sell. If it would be ordinary excel I would have used formula. =INDEX(Target!A:Q;MATCH(B2;Target!A:A;0);17-C2) or VLOOKUp with a HLOOKUP. Is it possible to do a similar formula in PowerPivot?

Best regards

 

[James02]

I don't believe it's possible in that type of formula. That formula references R1C1 and PowerPivot can only reference columns. Your best best is to pull the data you need via slicer into excel through a PowerPivot, then have those formula's reference the data.

 

[scottsen]

You almost certainly want to unpivot the targets so that you have lots of rows, instead of lots of columns... then we can get somewhere.