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.


Hi – not quite following you on the need to link or not to link categories, but regardless you can accomplish a non-linked scenario via the following Select formula.

Budget = select(Budget::Month,@Budget::Year:Month,@Year:Month)

You are selecting the values, and the keys are the year/month combinations.

Either way will work (linked vs. non linked) just a matter of preference on your part.

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