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.


Without seeing your model this is a bit of a guess but I think this is a good candidate for a SELECT or USING AS function. Take a look at this area of the on-line help to see if this is useful.


Feel free to post your model if you need further assistance.

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