# VLOOKUP to display an Array?

#### thesproing

##### Board Regular
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
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

##### MrExcel MVP
Perhaps try

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

#### thesproing

##### Board Regular
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.

Replies
2
Views
287
Replies
12
Views
595
Replies
7
Views
284
Replies
0
Views
813
Replies
4
Views
403

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.

### Which adblocker are you using?

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

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