Referencing data from one matrix to another

4.27K viewsTips and Techniques

I am trying to complete a matrix that compares Budget to Actual by Month.

I want the comparison matrix to be populated with the line item budgeted and actual spending amounts to come from 2 separate matrices, one for budgeted amounts and one for actual amounts.

My current approach is a formula I have entered for each month for Budget and Actual amounts – so I have 24 formulas to pick up the all the month’s spending amounts. I thought I could use the lookup formula but it requires all line items in the lookup array to be in ascending order so that won’t work. My line items in all 3 matrices are not in ascending order and are grouped. I have 13 different groups; each group has a number of different line items for different expenses. All 3 matrices are setup with identical line items for the rows for all spending descriptions.

The approach I would use for this in Excel would be to use vlookup. Any thoughts would be appreciated. Thanks.


I think you just need to link the Year and Month dimension in your comparison matrix with the budget and actual matrices. Right click and choose Link Category. Attached is an example and then you can do your budget formula with one formula as you desire instead of month by month.

You are viewing 1 out of 6 answers, click here to view all answers.