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

January	Act	Budget	%
New Store Openings	1	1	100
Relocates	2	2	100
Refits	1	2	50

<tbody>
</tbody>

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

	January			February
	Actual	Budget	%	Actual	Budget	%
New Store Openings	1	1	100	0	1	0
Relocates	2	2	100	2	2	100
Refits	1	2	50	1	1	100

<tbody>
</tbody>

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