Three variable "data table"

DMT

New Member
Joined
Jan 21, 2004
Messages
28
I would like to know if there is a way to create a 3 variable data table. Excel allows a 2-way table (i.e. inputs in rows and columns). However, I would like to know if I can do the same thing, but change one more variable. I'm not sure if there is a way with vlookup or offset or an if statement to do this.

Thanks,
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

DMT

New Member
Joined
Jan 21, 2004
Messages
28
Here is an example,

I want to pull in different line items from a table. Across the top of my table is dates. It is down the left hand side that I am struggling. I want to do 2 things (hence the 3 variable table). I want to look up a certain row number and do this based on a certain scenario. I have thought about using Excel's Scenario tool, but it's not what I'm after.

To be more clear, the value to be calculated in my two-dimensional data table is based on the hlookup formula. The hlookup looks up a certain column (forced by Excel Data Table) and then would look up a certain row (based on the left side variables in the Data Table). This is fine, but I want to do this also for three scenarios.

I don't know if I'm making any sense...
 

ddadmin2009

New Member
Joined
Apr 7, 2009
Messages
35
Can you provide a snapshot of the data you have. Looks like your problem can be solved using either couple of formulas or pivot tables.
 

DMT

New Member
Joined
Jan 21, 2004
Messages
28
Here is snapshot...

0.0 2009 2010 2011
81 $0 $0 $0
82 $0 $0 $0


The 0.0 is the number that the data table is calculating. Across the top are years and down the left are row numbers. The 0.0 is hlookup(year, array, row). I want the 0.0 to be a certain line item (either row 81 or row 82 from a table above, which hlookup takes care of) based on either scenario 1 or scenario 2. I don't know how to incorporate the two scenarios into this 2x2 data table.

Thanks.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,917
Get two Option Buttons from the Forms menu.
Label them Scenario1 and Scenario2.
Link them to a cell. (AA1 for example)
Use formulas similar to
=CHOOSE($AA$1, formulaForScenario1, formulaforScenario2)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

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
Top