Choosing specific table based on drop-down list selection

[broadhead077]

I'm trying to write a formula that will perform a calculation based on several inputs that are selected on a "data input" tab in my workbook. I have a tab with many tables that are differentiated mainly by race and gender. I have drop-down lists that allow the user to select "Male" or "Female" for the gender and "All", "White", "Black", etc. for race.

Some of the tables I have are separated as "White Males", "Black Females", and "All Males," just to name a few. I have a working formula that will pull data from a table based on other inputs, such as age and education level. My problem is that this formula only pulls data from a single table based on which table I had hard-coded into the formula. Here is the formula I have so far:

=INDEX(tbl_lpe_black_females_employed,MATCH(demo_education,tbl_lpe_black_females_employed[Education Level],0),3)

where "tbl_lpe_black_females_employed" is the specific table that I would like to be able to automatically change in the formula.

I would like for the user to be able to enter "White" and "Females", for example, and have those selections automatically update my above formula to select data from the appropriate table. I've searched and searched for an example of this but haven't had any luck.

Hope this all makes sense. If I need to post any more examples or formulas, I wil do so.

Thanks in advance!

 

[FitnessNerd]

Watch some tutorials on the CHOOSE function. That works exactly the way you want (it sounds like).
Index will do it too but I understand CHOOSE is easier and better.

You would specify the criteria in like a VLOOKUP and the look up table would be the one you CHOOSE it to look at based on the table name (or criteria).