Range names in array formula

SamS

Well-known Member
Joined
Feb 17, 2002
Messages
542
How do I make a vlookup return a range name in an array table? I have a spreadsheet with several ranges that holds budgeting and forecasting data but only a single graph. The idea is to select a particular product from a drop down combo box and the graph displays only that product. To this end I have an array formula:
{=index(data_area,e62,0)}
which works well if the data is all contiguous.

Where I am striking trouble is when I try to use a vlookup instead of "data_array" to return a range name but I get returned the dreaded #Value, I know there should be a simple answer but can't seem to see it. Any help would be much appreciated.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
On 2002-03-19 13:40, SamS wrote:
How do I make a vlookup return a range name in an array table? I have a spreadsheet with several ranges that holds budgeting and forecasting data but only a single graph. The idea is to select a particular product from a drop down combo box and the graph displays only that product. To this end I have an array formula:
{=index(data_area,e62,0)}
which works well if the data is all contiguous.

Where I am striking trouble is when I try to use a vlookup instead of "data_array" to return a range name but I get returned the dreaded #Value, I know there should be a simple answer but can't seem to see it. Any help would be much appreciated.

Not sure but is this what you are looking for?

=INDEX(INDIRECT(VLOOKUP(lookup-value,RangeList,2,0)),E62,0)

where Rangelist is a lookup table of ranges of inetrest.

Aladin
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

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