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.