Vlookup issues

I am trying to find a simple and sweet solution to a vlookup issue. I have several states and individual counties listed under those states. Lists are driven by indirect so only those counties that apply to the state are available.

So.. I know what state is selected.. I know that the county selected is only available for that state. I then have another column of data that I need to retrieve based on the county and I have labeled each state/county data.. For example I have Highlighted all the counties in GA listed in column A and the response I want in Column B and labeled the table_array GA_1... Each state table is listed the same with the State_1.

Is it possible to list multiple tables in a vlookup? =vlookup(county selected, GA_1 or TX_1 or FL_1, 2)?? Is it possible to reference a cell that has a label based on inputs? Cell B2 =concatenate(state abbr,"_1)... then have vlookup(county selected, B2,2)?? I tried this and it doesnt input as a label but as a cell reference. Is there a way to tell the formula that the content of the cell is a data label? I would like to avoid having to do a macro.. Thanks in advance

Can you attach a sample workbook?

No attachment

In B9 use =VLOOKUP(B7,INDIRECT(B5),2,0)

but first you need to change the scope of your named range to workbook instead of worksheet

I would also suggest removing B5 the table array and just using the state abbreviation as the named range.

No problem.

I only did two examples, GA and TX, but hopefully from this example you can follow the rest.

B3 >> select the state
B6 >> returns the table array -- named range
B9 >> using indirect the names are returned
B12 >> =IFERROR(VLOOKUP(B9,INDIRECT(B6),2,0),"")

Now when you change a state in B3, B9 is cleared and ready for the next input. Try TX now.

Note: You must enable macros

This link will explain the indirect better than I can...
http://www.contextures.com/xlFunctions05.html

I agree...I am the same way...need something to see Gald it worked for you

Don't forget to mark as solved if this satisfies your query.