Dynamically call named ranges in excel

[pjw23]

I need some help understanding why I cannot dynamically call a named range using a formula. I’ve attached an example spreadsheet as an example.

Inside the spreadsheet, I have a master table that is a named range. (Master_Table) Inside the master range are sub-ranges. (CLT3_Table and FLL2_Table) Depending on what sub-range is chosen from the dropdown, I would like to be able to lookup specific values within the range.

The challenge that I’m having is being able to dynamically call the site-specific named range through using either vlookup or (Index/Match). In the attached spreadsheet, what I need help with is the line labeled with a RED #5. This formula (Cell c26) needs to be able to pull the alias based on the site and port that are chosen using the (2) dropdowns. I can’t for the life of me figure out what I’m doing wrong and could really use some assistance. Thanks.
ExampleA.xlsx

[martindwilson]

i cant see any dynamic named range,where is it?
do you mean
=VLOOKUP(M3,INDIRECT(VLOOKUP(M7,Site_Table,2,FALSE)),10,FALSE)

[pjw23]

Martin,
Please read the original post. I was asking how to dynamically CALL a named range. Not how to use a dynamic named range. Hopefully that makes sense, if I'm missing something please let me know.

[pjw23]

Martin. I just realized your post had a formula to test. I didn't notice that on my phone. One I get in front of PC I will try that out. Thanks.

[FDibbins]

Perhaps if you explained what you are trying to do, rather than how you think you should be doing it, we could understand better what you want?

[pjw23]

Perhaps if you explained what you are trying to do, rather than how you think you should be doing it, we could understand better what you want?

I’m not sure how much better I can describe what I’m trying to do, but I’ll try my best.
Basically, I have an extremely long table with data in it similar to that posted in the example spreadsheet. This table contains switch port data for 26 different cities (4500 Ports). The spreadsheet is being used to assist with port zoning, port naming, and load balancing across the environment.

What I’m trying to accomplish is an easy way to query ports information based on the input values of SiteID and Port Number.

Open the example spreadsheet. In the spreadsheet there are (2) drop downs. One chooses the Site, and the other chooses the Port. With that, I need to be able to pull the port alias and other relevant information, based on those two selections.

It seems the problem is that it’s not possible to call a named range through a cell reference. I’m trying to confirm that and if so, find a way around it. For example, in the spreadsheet, entering a simple formula such as =MATCH(M7,C23) returns as N/A. whereas, the formula =MATCH(2,CLT3_IDs), returns the value "2". (NOTE: Look at name manager for more clarification)
it doesn’t appear that calling a named range by using that name through a cell reference is possible. I need to find a way around that problem.

[pjw23]

i cant see any dynamic named range,where is it?
do you mean
=VLOOKUP(M3,INDIRECT(VLOOKUP(M7,Site_Table,2,FALSE)),10,FALSE)

martin, this at least returns a value, but it doesn't change as my inputs change. Any other ideas.

[pjw23]

i cant see any dynamic named range,where is it?
do you mean
=VLOOKUP(M3,INDIRECT(VLOOKUP(M7,Site_Table,2,FALSE)),10,FALSE)

Martin,

Based on your formula, I made it work....THANK YOU...
=VLOOKUP(M7,INDIRECT(VLOOKUP(M3,Site_Table,2,FALSE)),10,FALSE)
I've never used indirect before and need to understand it better, but it's working...thanks again.