INDEX + MATCH to match 2 columns, one is a merged cell?

[Brojangels]

I am trying to automate a process so that I can import a budget from QuickBooks and do less manual work in the future. The workbook would have 2 tabs, a "permanent" tab that is formatted the way I like, and most of the cells would be INDEX+MATCH lookups to the other tab; and the "import" tab (called QB) that would change on a monthly or some periodic basis.

I thought I had it figured out, because I got the January "budget" and "actual" columns to work, but when I copied it to February, the actual matched on both tabs but the "budget" numbers were referencing January on the QB tab.

Here's a general idea of the layout:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]January[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]February[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD][/TD]
[TD]Actual[/TD]
[TD]Budget[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Currently my formula for "February Budget" is as follows:
{=INDEX(QB!$A$2:$CE$129,MATCH('Budget to Actual'!$C25,QB!$F$2:$F$129,0),MATCH(1,(QB!$2:$2='Budget to Actual'!L$2)*(QB!$3:$3='Budget to Actual'!L$3),0))}

F is the column on the QB tab with the account numbers,
J:N is the column range for "February" in row 2 (formulas are just using L)
L is the column on the "Budget to Actual" tab for "Budget" underneath the merged cell "February"

When I copy this formula to the "Budget" and "Actual" columns for additional months, again, the "Actual" matches QB correctly, but the "Budget" shows January's number in every month.

Any ideas???

 

[igold]

Un-merge the cells then highlight the cells and go to Format Cells -> Alignment -> Horizontal -> and use "Center Across Selection" instead.

igold

 

[Brojangels]

Hmm... Well, that's a cool new formatting tool I just learned, but it is still not displaying the right result. Now instead of displaying January's Budget amount, it is saying #N/A in the other months' Budget columns. Any other ideas?

 

[igold]

To be honest, I did not look over your formulas. Have you checked those now that the cells are formatted in a more friendly manner.

 

[Brojangels]

Yes, I checked the formulas to make sure they were referencing whatever cell actually contained "February" etc, regardless of where it appeared.

FWIW, I think the issue has something to do with the combination of referencing the "February" or whatever month cell, and also the cell that says "Budget" for each month. What I do not understand, though, is why the formula works for "Actual" every month, but "Budget" only displays January. I don't see why one would work and not the other.

 

[Brojangels]

Replying because I am still scratching my head over this. Not sure if this will get re-bumped to the top for visibility, but any help is appreciated.