VLOOKUP to display an Array?

thesproing

Board Regular
Joined
Jul 16, 2007
Messages
76
Hi,

Can VLOOKUP be used to return an array, rather than just a value?

i will try and explain.

i have several sheets, all displaying tables for different years, eg, 2006,2007, 2008 etc etc, shown below...

ttp://s188.photobucket.com/albums/z303/thesproing/?action=view&current=array.jpg

however, these sheets are just for entering data into, and i have a seperate worksheet to display the information;

http://s188.photobucket.com/albums/z303/thesproing/?action=view&current=vlookup.jpg

what i want is for the table here to display the info in the corresponding sheet, depending on what value is entered into cell B2.

Any ideas?????

thanks.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

thesproing

Board Regular
Joined
Jul 16, 2007
Messages
76
btw, at the moment i am using a formula like....

"=IF($B$2=2008,'2007-2008'!O3,IF($B$2=2007,'2006-2007'!O3,IF($B$2=2006,'2005-2006'!O3,IF($B$2=2009,'2008-2009'!O3,IF($B$2=2010,'2009-2010'!O3,IF($B$2=2011,'2010-2011'!O3,IF($B$2=2012,'2011-2012'!O3,IF($B$2=2013,'2012-2013'!O3,0))))))))

which is far too cumbersum and will only let me use "IF" so many times.
 

thesproing

Board Regular
Joined
Jul 16, 2007
Messages
76
yeah that seems almost perfect, except that the value of the cell being referenced doesnt change...ie i want O3 to change depending on what cell the formua is in,

At the moment it is acting as if it is $O3,

Any ideas? INDIRECT is a new function to me.

Thanks again.
 

Forum statistics

Threads
1,181,366
Messages
5,929,549
Members
436,677
Latest member
CathalP1992

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