VLOOKUP to display an Array?

[thesproing]

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.

 

[thesproing]

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.

 

[barry houdini]

Perhaps try

=INDIRECT("'"&B2-1&"-"&B2&"'!O3")

 

[thesproing]

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.