Index Match Return Second Column, not first

[CPR83]

Hello,

I am building a template based on a auto-generated template coming out of our financial system. Right now the source data is
July August September
[TABLE="width: 500"]
<tbody>[TR]
[TD]Area[/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[/TR]
[TR]
[TD]Area1[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD]12[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Area2[/TD]
[TD]12[/TD]
[TD]18[/TD]
[TD]12[/TD]
[TD]18[/TD]
[TD]13[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]Area3[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]4[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Area4[/TD]
[TD]2[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]


My end goal is to create an index match to return a table that looks like this:

Actuals
[TABLE="width: 500"]
<tbody>[TR]
[TD]Area[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[/TR]
[TR]
[TD]Area1[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Area2[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Area3[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Area4[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Right now I have it set up to index match successfully on the first column for Actuals. I want to create another table for just the budget. I know I can do this with a simple link but I like having these checks in place. Right now my index match checks against a cell with "Actuals" against the column heading of the source table.
This is my current formula and it works well for the Actuals table:
=INDEX('Total'!$C$7:$AN$22,MATCH(Sheet1!$B8,'Total'!$A$7:$A$22,0),MATCH(Sheet1!E$4,'Total'!$C$4:$AN$4,0),MATCH($B$3,''!$C$5:$AN$5,0))

Only problem is I can't figure out how to return the budget column since the formula above is returning the first column automatically.

I've been searching for an answer but can't seem to come up with a solution.

 

[Scott T]

Book1
	A	B	C	D	E	F	G
2		July	July	August	August	September	September
3	Area	Actual	Budget	Actual	Budget	Actual	Budget
4	Area1	10	15	12	15	12	18
5	Area2	12	18	12	18	13	18
6	Area3	5	9	8	9	4	6
7	Area4	2	6	5	8	4	9
8
9
10	Actual
11	Area	July	August	September	October	November
12	Area1	10	12	12
13	Area2	12	12	13
14	Area3	5	8	4
15	Area4	2	5	4
16
17
18	budget
19	Area	July	August	September	October	November
20	Area1	15	15	18
21	Area2	18	18	18
22	Area3	9	9	6
23	Area4	6	8	9
Sheet2

Cell Formulas
Range	Formula
B12	{=INDEX($B$4:$G$7,MATCH($A12,$A$4:$A$7,0),MATCH(B$11&$A$10,$B$2:$G$2&$B$3:$G$3,0))}
B20	{=INDEX($B$4:$G$7,MATCH($A20,$A$4:$A$7,0),MATCH(B$11&$A$18,$B$2:$G$2&$B$3:$G$3,0))}
C12	{=INDEX($B$4:$G$7,MATCH($A12,$A$4:$A$7,0),MATCH(C$11&$A$10,$B$2:$G$2&$B$3:$G$3,0))}
C20	{=INDEX($B$4:$G$7,MATCH($A20,$A$4:$A$7,0),MATCH(C$11&$A$18,$B$2:$G$2&$B$3:$G$3,0))}
D12	{=INDEX($B$4:$G$7,MATCH($A12,$A$4:$A$7,0),MATCH(D$11&$A$10,$B$2:$G$2&$B$3:$G$3,0))}
D20	{=INDEX($B$4:$G$7,MATCH($A20,$A$4:$A$7,0),MATCH(D$11&$A$18,$B$2:$G$2&$B$3:$G$3,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Swayzy]

Personally I like using sumifs and dates Excel understands. By using sumifs then it is much easier to use a criteria for summing.

So in your case I would replace the dates if they arent in the real Excel way and them. Then build your separate tables that also have dates on top. You can format them as displaying the month name not an actual date.

Set the dates with the eomonth function. For the File that comes from the system you will need do a double month setup so there are dates for both bud and act. Just set two cells to end of july then to the right use two cells with eomonth functions and ref to those two cells. Then you mark both cells with the eomonth functions and drag to the right

 

[Fluff]

Maybe
INDEX('Total'!$C$7:$AN$22,MATCH(Sheet1!$B8,'Total'!$A$7:$A$22,0),MATCH(Sheet1!E$4,'Total'!$C$4:$AN$4,0)+1)