Using Index and Match for 1 column and two rows

[Legacy 351788]

Hi,

I have a spreadhseet with far too much information on it. I want to add a tab that will pull just the information I want from it. I've been trying to do this using INDEX and MATCH. But I think where the issues is, I need it to use information from two different rows. One row is the month, and the second row is ACTUAL, BUDGET and has this for each month. So, I maybe confusing it withhaving the same two words repeared 12 times. Because when I put the below formula in, the value it returns is from the cell before it. The formula i'm using is -

IFERROR(INDEX('P&L Forecast'!$D$9:$BJ$92, MATCH('P&L Summary'!$C$4:$C$42,'P&L Forecast'!$C$9:$C$92,0), MATCH('P&L Summary'!$D$2:$AN$2, 'P&L Forecast'!$D$7:$BJ$7,0),(AND(MATCH('P&L Summary'!$D$3:$AP$3, 'P&L Forecast'!$D$6:$BJ$6,0)))),"")

Is there a way around this where I don't have to change the formula for each row or column or am I just being super lazy?

THANK YOU

 

[jasonb75]

First of all, you only need a single relative reference for the first argument of MATCH, using the whole range makes your formulas messy and confusing, even more so when those ranges overlap with the second argument (that is just plain bad).

From what I can make out, it looks as if you need something like.

=IFERROR(INDEX('P&L Forecast'!$D$9:$BJ$92, MATCH('P&L Summary'!$C4,'P&L Forecast'!$C$9:$C$92,0), MATCH('P&L Summary'!$D2&$D3, 'P&L Forecast'!$D$7:$BJ$7&'P&L Forecast'!$D$6:$BJ$6,0)),"")

But your explanation is anything but clear, so I could be way off.