pull a value with Index-Match formula when the source is changing

[Yakovzim]

I need to present a summary of a P&L report every month in two different currencies, and the source data is a BI report.
I need to sum up by two different categories. The BI report looks like that:

		Group Currency#1	Group Currency#2
Item	Company	1	2	3	1	2	3
Deposits	1000	61	54	28	4	23	57
	2000	50	27	58	22	97	14
	3000	71	92	83	56	12	12
Loans	1000	25	97	85	37	93	69
	2000	0	31	69	36	6	31
Exchange rate differences	2000	84	3	3	15	66	88

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

The example above shows the data from March - every item appears in two different currencies and spread out by month. I'm pulling certain information based on 'concatenate' function which joins the item and the company and on it I use the Index-Match.

The problem is that in the following month (let's say April) I have two new columns (one <gs id="6e2aff71-eef6-4048-944c-470480f8548b" ginger_software_uiphraseguid="db020b36-a346-4824-8c51-2f5eee9615a4" class="GINGER_SOFTWARE_mark">for</gs> Group currency 1 in April and one for GC2 in April). That means that the Index array I defined for let's say 'deposits+company 1000' shifts.

Is there any way to change the index so that it would fit in every month?

Thanks for the help,
Yaakov

Excel <gs id="6b1acd5b-94b7-412e-b2e6-95714420573d" ginger_software_uiphraseguid="bb982d72-143a-4bd8-9325-84b524efb861" class="GINGER_SOFTWARE_mark">ver</gs>. 2010
Win -7

 

[cbatrody]

Hi,

Do you have different tabs for each month or everything is in one tab (worksheet)?