Vlookup with a scenario

L

lorenzoc

New Member
Joined
Feb 21, 2013
Messages
26
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[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Jan
[/TD]
[TD]Feb
[/TD]
[TD]Mar
[/TD]
[TD]Apr
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]random lookup value
[/TD]
[TD]=vlook..
[/TD]
[TD]=vlook..
[/TD]
[TD]=vlook..
[/TD]
[TD]=vlook..
[/TD]
[TD][/TD]
[TD][/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]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Maybe something like this

Sheet A

A B C ...................M[TABLE="width: 621"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Mar
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Apr
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]May
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Jun
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Jul
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Aug
[/TD]
[TD="class: xl65, width: 60, bgcolor: transparent"]Sep
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Oct
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Nov
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Dec
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Value
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]30
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]40
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]60
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]70
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]80
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]90
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]110
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]120
[/TD]
[/TR]
</tbody>[/TABLE]


Sheet B

A B C ..................M[TABLE="width: 624"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Mar
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Apr
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]May
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Jun
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Jul
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Aug
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Sep
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Oct
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Nov
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Dec
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Value
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]100
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]101
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]102
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]103
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]105
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]106
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]107
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]108
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]109
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]110
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]111
[/TD]
[/TR]
</tbody>[/TABLE]


Summary

A B C D.................................O[TABLE="width: 720"]
<tbody>[TR]
[TD="class: xl65, width: 64, bgcolor: transparent"]Scenario
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]Months
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]A
[/TD]
[TD="class: xl65, bgcolor: transparent"]B
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"]Jan
[/TD]
[TD="class: xl65, bgcolor: transparent"]Feb
[/TD]
[TD="class: xl65, bgcolor: transparent"]Mar
[/TD]
[TD="class: xl65, bgcolor: transparent"]Apr
[/TD]
[TD="class: xl65, bgcolor: transparent"]May
[/TD]
[TD="class: xl65, bgcolor: transparent"]Jun
[/TD]
[TD="class: xl65, bgcolor: transparent"]Jul
[/TD]
[TD="class: xl65, bgcolor: transparent"]Aug
[/TD]
[TD="class: xl65, bgcolor: transparent"]Sep
[/TD]
[TD="class: xl65, bgcolor: transparent"]Oct
[/TD]
[TD="class: xl65, bgcolor: transparent"]Nov
[/TD]
[TD="class: xl65, bgcolor: transparent"]Dec
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]2
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]10
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]102
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]103
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]104
[/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[TD="class: xl65, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0
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
 
Upvote 0
lorenzoc said:
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
Click to expand...

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.
 
Upvote 0
According to your PM

Sheet Actual

A B C D E (headers in row 4)
[TABLE="class: grid, width: 650"]
<TBODY>[TR]
[TD="width: 266, bgcolor: transparent"]Accounts
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="width: 64, bgcolor: transparent"]Feb
[/TD]
[TD="width: 64, bgcolor: transparent"]Mar
[/TD]
[TD="width: 64, bgcolor: transparent"]Apr
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Donations - Non Deductible
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]14
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Calgarycharitable Donations - Deductible
[/TD]
[TD="bgcolor: transparent, align: right"]18
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Political Contributions
[/TD]
[TD="bgcolor: transparent, align: right"]31
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]31
[/TD]
[TD="bgcolor: transparent, align: right"]35
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Advertising & Public Relations
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[TD="bgcolor: transparent, align: right"]42
[/TD]
[TD="bgcolor: transparent, align: right"]39
[/TD]
[TD="bgcolor: transparent, align: right"]44
[/TD]
[/TR]
</TBODY>[/TABLE]


Sheet Forecast

A B C D E
[TABLE="class: grid, width: 650"]
<TBODY>[TR]
[TD="width: 266, bgcolor: transparent"]Accounts
[/TD]
[TD="width: 64, bgcolor: transparent"]Jan
[/TD]
[TD="width: 64, bgcolor: transparent"]Feb
[/TD]
[TD="width: 64, bgcolor: transparent"]Mar
[/TD]
[TD="width: 64, bgcolor: transparent"]Apr
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Donations - Non Deductible
[/TD]
[TD="bgcolor: transparent, align: right"]10
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]13
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Calgarycharitable Donations - Deductible
[/TD]
[TD="bgcolor: transparent, align: right"]20
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Political Contributions
[/TD]
[TD="bgcolor: transparent, align: right"]30
[/TD]
[TD="bgcolor: transparent, align: right"]31
[/TD]
[TD="bgcolor: transparent, align: right"]32
[/TD]
[TD="bgcolor: transparent, align: right"]33
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Advertising & Public Relations
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[TD="bgcolor: transparent, align: right"]41
[/TD]
[TD="bgcolor: transparent, align: right"]42
[/TD]
[TD="bgcolor: transparent, align: right"]43
[/TD]
[/TR]
</TBODY>[/TABLE]


Sheet Summary


A B C D E (B1=Actual)

[TABLE="class: grid, width: 650"]
<TBODY>[TR]
[TD="width: 266, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Actual
[/TD]
[TD="width: 64, bgcolor: transparent"]Forecast
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Scenario
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent, align: right"]3
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Accounts
[/TD]
[TD="bgcolor: transparent"]Jan
[/TD]
[TD="bgcolor: transparent"]Feb
[/TD]
[TD="bgcolor: transparent"]Mar
[/TD]
[TD="bgcolor: transparent"]Abr
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Donations - Non Deductible
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]11
[/TD]
[TD="bgcolor: transparent, align: right"]12
[/TD]
[TD="bgcolor: transparent, align: right"]13
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Calgarycharitable Donations - Deductible
[/TD]
[TD="bgcolor: transparent, align: right"]18
[/TD]
[TD="bgcolor: transparent, align: right"]21
[/TD]
[TD="bgcolor: transparent, align: right"]22
[/TD]
[TD="bgcolor: transparent, align: right"]23
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Political Contributions
[/TD]
[TD="bgcolor: transparent, align: right"]31
[/TD]
[TD="bgcolor: transparent, align: right"]31
[/TD]
[TD="bgcolor: transparent, align: right"]32
[/TD]
[TD="bgcolor: transparent, align: right"]33
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Advertising & Public Relations
[/TD]
[TD="bgcolor: transparent, align: right"]40
[/TD]
[TD="bgcolor: transparent, align: right"]41
[/TD]
[TD="bgcolor: transparent, align: right"]42
[/TD]
[TD="bgcolor: transparent, align: right"]43
[/TD]
[/TR]
</TBODY>[/TABLE]


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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Help with a formula
Replies
6
Views
258
paulsolar
P
F
  • Solved
Copy paste from one Excel workbook to another with same sheet names and update dates in range
Replies
8
Views
623
Skiier89
S
D
  • Locked
  • Question Question
Could you please write a code for the following scenario?
Replies
1
Views
217
Fluff
Fluff
A
  • Question Question
Enter formula with VLOOKUP referencing another file
Replies
4
Views
162
Alan_CT06
A
Jyggalag
  • Question Question
Date formula
2
Replies
11
Views
310
Alex Blakenburg
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top