Vlookup with a scenario

[lorenzoc]

Hello again,

I need help with a vlook up formula. What i would like from the vlook up is illustrated below. There are two seperate pull sheets, well call them a & b. The user starts by selecting a scenario. The scenario indicates which months would be pulled from where. For example if the scenario choosen was 2+2, jan + feb would be pulled from sheet a and march and apr would be pulled from sheet b.
The example illustrated below shows a 1+3 scenario meaning jan would be pulled from sheet a and 3 months pulled from sheet b. I know this formula is possible so if anyone can help me out I would be forever grateful.

Pull sheets (a or b)
Scenario=1+3

Jan

Feb

Mar

Apr

random lookup value

=vlook..

=vlook..

=vlook..

=vlook..

<tbody>
</tbody>

 

[Marcelo Branco]

Maybe something like this

Sheet A

A B C ...................M

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Value	10	20	30	40	50	60	70	80	90	100	110	120

<tbody>
</tbody>

Sheet B

A B C ..................M

	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Value	100	101	102	103	104	105	106	107	108	109	110	111

<tbody>
</tbody>

Summary

A B C D.................................O

Scenario

Months

A

B

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

2

3

10

20

102

103

104

<tbody>
</tbody>

Formula in D3
=IF($A$3+$B$3>=COLUMNS($D$3:D3),HLOOKUP(D$2,INDIRECT("'"&IF(COLUMNS($D3:D3)<=$A$3,$A$2,$B$2)&"'!$B:$M"),2,0),"")
copy across

M.

 

[lorenzoc]

Unfortuantly, I am having difficulty interepreting what you are saying. What do the A B C D represent?

i have two sheets called actual and forecast, and then scenarios like 1+11, so 1 from sheet 1 month from actuals and 11 from forecasts. Are we thinking about the same thing?

thanks so much Marcello

 

[Marcelo Branco]

Unfortuantly, I am having difficulty interepreting what you are saying. What do the A B C D represent?

i have two sheets called actual and forecast, and then scenarios like 1+11, so 1 from sheet 1 month from actuals and 11 from forecasts. Are we thinking about the same thing?

thanks so much Marcello

A B C D...means column A, Column B, Column C ....

Assuming the data (the months) are in B1:M1 on sheets Actual and Forecast and the values in B2:M2 .(adjust to suit), in my example above, on Summary sheet change A2 to Actual and B2 to Forecast, ie, put the sheet names in A2 and B2.

Hope this is what you need

M.

 

[Marcelo Branco]

According to your PM

Sheet Actual

A B C D E (headers in row 4)

Accounts	Jan	Feb	Mar	Apr
Donations - Non Deductible	12	10	12	14
Calgarycharitable Donations - Deductible	18	22	23	23
Political Contributions	31	30	31	35
Advertising & Public Relations	40	42	39	44

<TBODY>
</TBODY>

Sheet Forecast

A B C D E

Accounts	Jan	Feb	Mar	Apr
Donations - Non Deductible	10	11	12	13
Calgarycharitable Donations - Deductible	20	21	22	23
Political Contributions	30	31	32	33
Advertising & Public Relations	40	41	42	43

<TBODY>
</TBODY>

Sheet Summary


A B C D E (B1=Actual)

	Actual	Forecast
Scenario	1	3
Accounts	Jan	Feb	Mar	Abr
Donations - Non Deductible	12	11	12	13
Calgarycharitable Donations - Deductible	18	21	22	23
Political Contributions	31	31	32	33
Advertising & Public Relations	40	41	42	43

<TBODY>
</TBODY>

Formula in B5
=IF($B$2+$C$2>=COLUMNS($B5:B5),VLOOKUP($A5,INDIRECT("'"&IF($B$2>=COLUMNS($B5:B5),$B$1,$C$1)&"'!$A:$M"),COLUMNS($B5:B5)+1,0),"")

copy across and down

M.

 

[lorenzoc]

heres the formula I have been trying

=IF($C$2+$D$2>=COLUMNS($B$5:B5),VLOOKUP(A5,INDIRECT(""&IF($C$2>=COLUMNS($B$5:B5),Actuals,Forecast)&"'!$A:$M"),COLUMNS($B$5:B5)+1,0),"")

I get a NAME error at the actuals and forecast part I highlighted above. Those are the names of my two other tabs. I even tried putting Actual!

any thoughts? your patience Marcelo is appreciated

thanks,

Lorenzo

 

[lorenzoc]

heres the formula I have been trying

=IF($C$2+$D$2>=COLUMNS($B$5:B5),VLOOKUP(A5,INDIRECT(""&IF($C$2>=COLUMNS($B$5:B5),Actuals,Forecast)&"'!$A:$M"),COLUMNS($B$5:B5)+1,0),"")

I get a NAME error at the actuals and forecast part I highlighted above. Those are the names of my two other tabs. I even tried putting Actual!

any thoughts? your patience Marcelo is appreciated

thanks,

Lorenzo

 

[lorenzoc]

Got it to work!

Amazing,
THANKS SO MUCH MARCELO!!! YOUR A GREAT HELP.

 

[Marcelo Branco]

Got it to work!

Amazing,
THANKS SO MUCH MARCELO!!! YOUR A GREAT HELP.

Great!

Glad for helping


M.