Dynamic rate tables: How to change multiplied cell by drop down list

My goal is to have an average value (returned by 2 user inputted values) to be multiplied by a unique rate that is from either of 2 tables.

I have 2 drop-down lists: The 1st representing 2 tables (Table 1 & Table 2) and the 2nd representing the columns withing Tables 1 & Tables 2 (Healthcare, Education, Etc.)

I want it to when a user selects "Table 1" from the 1st drop-down list and "Healthcare" from the 2nd drop-down list it will call the rate in Table 1 - Healthcare cell multiply the Avg.tkt by the rate from Table 1 - Healthcare column (i.e. 2.00% x $100) and return it in a new cell.

I've already achieved this by nest IF statements:

=IF(C4="Table1",IF(C5="Healthcare",D31*J7,IF(C5="Education",D31*K7,C5="Charity",D31*L7),IF(C4="Table2",IF(C5="Healthcare",D31*J7,IF(C5="Education",D31*K7,C5="Charity",D31*L7)))))

But would like to know if there is a more efficient way of producing these results?
I would like to reduce the use of nesting IF statements (or at least cut down on the amount).

If you don't want to or can't join the tables together as another answer suggested, you could use an IF statement and nest an INDEX and MATCH formula within:

=IF($B$4="Table 1",INDEX($A$7:$C$10,MATCH(D$1,$A$7:$A$10,0),MATCH($B$5,$A$7:$C$7,0)),INDEX($A$12:$C$15,MATCH(D$1,$A$12:$A$15,0),MATCH($B$5,$A$12:$C$12,0)))

This checks whether the table selected is Table 1. If it is then it looks up that rate metric for that specific industry in the Table 1 range of data. If the dropdown is not Table 1, it is Table 2 so it does the same lookup but for the Table 2 range of data.

In Excel there are multitude of approaches to resolve a problem. Using Table Names with INDIRECT() Function can also be accomplished, however using INDIRECT() can be avoided as it is single-threaded and so defeats Excel’s multi-threaded calculation engine. Still I am sharing the solution along with the other ways, you can try the one best suits with your query.

Approach One: --> Using INDIRECT() & TOROW()

• Formula used in cell H4 --> It spills to right!

=TOROW(INDIRECT(E1&"["&F1&"]"))

• Or, Formula used in cell H9 --> Without #SPILL! --> It needs to fill right!

=INDEX(INDIRECT($E$1&"["&$F$1&"]"),COLUMN(A1))

Approach Two: --> Using XLOOKUP() with INDEX() and CHOOSE() or IF()

• Formula used in cell H14 --> Using IF()

=INDEX(XLOOKUP($F$1,TableOne[#Headers],
IF($E$1="TableOne",TableOne,TableTwo)),COLUMN(A1))

• Or, Formula used in cell H19 --> Using CHOOSE()

=INDEX(XLOOKUP($F$1,TableOne[#Headers],
CHOOSE(N($E$1<>"TableOne")+1,TableOne,TableTwo)),COLUMN(A1))

Note: In all of the above formulas I am referencing structured table viz. TableOne & TableTwo . Please adjust the ranges as per your suit.

I would recommend to join those 2 tables and change structure a bit so you can avoid IF statements (imagine if you need to use even more tables) and use something like this:

=SUM(($D$9:$E$9=D4)*($B$10:$B$15=C4)*($C$10:$C$15=E4)*($D$10:$E$15))

Result:

Use that "filtered" value for your further calculations.