Referencing data from one matrix to another
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.
[url:2yuel6bl]http://www.quantrix.com/help_resources/5.1/webhelp/quantrix.htm#using_the_select_function.htm[/url:2yuel6bl]
Feel free to post your model if you need further assistance.