Excel - Index Match with 3 criteria, pulling from table generated from Power Query

0
votes

I'm trying to pull in the exact qty required from the table Master_Query table. The 3 criteria i need to match it with is the Part Number, the WeekNumber & the Date. I keep getting #REF! in my row for all except 1 cell. What is wrong with my code? Are there certain limitation with index match when pulling from a table generated by power query? Below is the code and images of the workbook

Edit: Just to specify in the page that I need to pull my data in, I am pulling in data for row 1081 starting at cell BS1081 and that data is to reflect only date 12/31 and fill in WeekNumber 1,2,3,4..etc pertaining to date 12/31. The next row down 1082 will start on cell BT1082 and that data is to reflect only date 1/07 for WeekNumber 1,2,3,4..etc.

=INDEX(Master_Query[Qty Required],MATCH('Waterfall 
2018'!$R$1081,Master_Query[Part Number],0),MATCH('Waterfall 
2018'!BS2,Master_Query[WeekNumber],0),MATCH($BS$1,Master_Query[Date],0))

Where I need to pull in the data (first cell BS1081) and then every proceeding cell in that row. enter image description here

Where the data is being pulled from enter image description here

1
excelmatchpowerquery

1 Answers

0
votes

Unless I'm misunderstanding what you are trying to do, I think you want to be using a SUMIFS formula here. Power Query data is just a normal Excel table once it's loaded into Excel.

=SUMIFS(Master_Query[Qty Required], Master_Query[Part Number], $R1081, Master_Query[WeekNumber], BS$2, Master_Query[Date], BS$1)