LookUp Help

[Bennets04]

Good morning all,

I have a question regarding a lookup formula I'm trying to work,

So i have the following SUMMARY sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]January[/TD]
[TD]Act[/TD]
[TD]Budget[/TD]
[TD]%[/TD]
[/TR]
[TR]
[TD]New Store Openings
[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Relocates[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Refits[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]50[/TD]
[/TR]
</tbody>[/TABLE]

Now the January cell (A1) is a drop down so when February is selected the data changes

On my YEAR sheet, i have the following

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]January[/TD]
[TD][/TD]
[TD][/TD]
[TD]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]New Store Openings[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Relocates[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]100[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Refits[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]50[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I can't seem to get the formula to register the month change on the summary and return the required month's data

Hope someone can help

Many thanks
Steve

 

[snjpverma]

use Index & Match formula for this to work

 

[Bennets04]

Any ideas on what that would look like as I'm fine with SumIF's and VLookups etc but not those

 

[snjpverma]

In Summary sheet,

in B2 and copied down.
=INDEX(Year!$B$1:$G$5,MATCH(Summary!$A2,Year!$A$1:$A$5,0),MATCH(Summary!$A$1,Year!$B$1:$G$1,0))

in C2 and copied down
=INDEX(Year!$B$1:$G$5,MATCH(Summary!$A2,Year!$A$1:$A$5,0),MATCH(Summary!$A$1,Year!$B$1:$G$1,0)+1)

in D2 and copied down
=INDEX(Year!$B$1:$G$5,MATCH(Summary!$A2,Year!$A$1:$A$5,0),MATCH(Summary!$A$1,Year!$B$1:$G$1,0)+2)

assuming that your data starts on A1

 

[Bennets04]

Works fine going down, how about when i drag the formula right?

 

[snjpverma]

Works fine going down, how about when i drag the formula right?

In B2, copy across and down.

=INDEX(Year!$B$1:$G$5,MATCH(Summary!$A2,Year!$A$1:$A$5,0),MATCH(Summary!$A$1,Year!$B$1:$G$1,0)+COLUMN()-2)

change the above highlighted references as per your requirement though

 

[Bennets04]

You are incredible!!

Thank you so much!

 

[snjpverma]

Glad to have helped