[Google Sheets] using named ranges in a drop down

I am trying to create a chart showing the most sold items in each aisle in a grocery store. I could easily create a different chart for each aisle using index and match or VLOOKUP, But I would prefer creating a single chart that can be changed using a drop down list. I figured I could make the drop down list contain the name of each aisle, and rename the appropriate range to correspond ( IE: if the information in aisle 1 is A1:A9, i could define that range to be called aisle1). When i attempt to set the range of the VLOOKUP function to my drop down list it says it is evaluating to an out of bounds range. If i go directly into my chart and set the range to "aisle1" then the formula works with no problems. Is there a way for my drop down list to contain "aisle1,asile2,asile3,...." If i define each aisles data to its corresponding name, and then for my VLOOKUP range for each cell in my chart just = the cell where my drop down is?

are you able to upload a text file, containing a link to a copy of your g-sheet (that, where necessary, has confidential information removed and replaced with sample data).. with edit privilege given to anyone with the link (because it is a copy, not the original).

I have an example sheet made up that has my problem in it, but Its not allowing me to post links "You are not allowed to post any kinds of links, images or videos until you post a few times." is there anyway around this?

yes, as mentioned in #2, upload a text file that has the link.
do this by following the instructions in the yellow banner above your first post.

ah i see, thank you.
Example Problem GS.txt

by changing your data into a table (preferred option as easier to craft solutions ), you can use an INDEX MATCH combination (which is a somewhat preferred method over VLOOKUP), like this in cell K2:

Please Login or Register  to view this content.

Screen Shot 2021-11-12 at 10.10.53 pm.png

please see your G-Sheet, i have created this on its own tab

Thats great! thank you very much for your help

i have just added in the named ranges for Stores, Aisles, and StoreData.

click on the result boxes K2 to O2 (row presentation) and K5 to K9 (column presentation) to see the formulas using the named ranges, and also look at the data validation for cells I2 and J2 which also using the same named ranges.

so now when you want to expand the range, you wont need to edit the formulas or data validation themselves, you will only need to expand the range of the named ranges