Generating result from multiple tables using multiple variables

[Friikijs]

Hello excel gurus!
I will try to be be as comprehensible as possible.

I have a calculation of data where I have to fill 3 cells from drop down menus to get a result.
1st cell indicates the table I have to use for the other data (lets name them table1, table2, table3)
2nd cell indicates row headline parameters (lets name them a, b, c, d, e, f)
3rd cell indicates column headline parameters (lets name them 1, 2, 3, 4, 5, 6)

Lets say we put Table2, C and 4, now the result has to come from the 2nd table and searched in the cell located where the row and column names are C and 4, the value of c4t2.

Assumptions before formulas:
*name respective table data to Table1Data, Table2Data, Table3Data
**name respective table row name Table1Row, Table2Row, Table3Row
***name respective table column name Table1Column, Table2Column, Table3Column
**** I have to give out a result of "No data!" if the located value is 0.

Formula for selecting data from 1 table:
=IF(INDEX(Table1Data;MATCH(D7;Table1Column;0);MATCH(D5;Table1Row;0))=0;"No data!";INDEX(Table1Data;MATCH(D7;Table1Column;0);MATCH(D5;Table1Row;0)))

Now I had to implement the 1st variable - table name into formula. I tried to do it with If function like this:
=IF(D3=”Table1”;IF(INDEX(Table1Data;MATCH(D7;Table1Column;0);MATCH(D5;Table1Row;0))=0;"No data!";INDEX(Table1Data;MATCH(D7;Table1Column;0);MATCH(D5;Table1Row;0)));IF(D3=”Table2”; IF(INDEX(Table2Data;MATCH(D7;Table2Column;0);MATCH(D5;Table2Row;0))=0;"No data!";INDEX(Table2Data;MATCH(D7;Table2Column;0);MATCH(D5;Table2Row;0)));IF(D3=”Table3”; IF(INDEX(Table3Data;MATCH(D7;Table3Column;0);MATCH(D5;Table3Row;0))=0;"No data!";INDEX(Table3Data;MATCH(D7;Table3Column;0);MATCH(D5;Table3Row;0))))))

It now only gives #Name error.

Any help would be really appreciated. Since I can't upload an attachment please tell me if you understand the problem.

 

[Friikijs]

[TABLE="class: grid, width: 293, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]1st variable:[/TD]
[TD="colspan: 5, align: center"]Table2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]2nd variable:[/TD]
[TD="colspan: 5, align: center"]c[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3rd variable:[/TD]
[TD="colspan: 5, align: center"]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Result:[/TD]
[TD="colspan: 5, align: center"]4ct2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7, align: center"]Table1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1at1[/TD]
[TD]1bt1[/TD]
[TD]1ct1[/TD]
[TD]1dt1[/TD]
[TD]1et1[/TD]
[TD]1ft1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2at1[/TD]
[TD]0[/TD]
[TD]2ct1[/TD]
[TD]2dt1[/TD]
[TD]2et1[/TD]
[TD]2ft1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3at1[/TD]
[TD]3bt1[/TD]
[TD]0[/TD]
[TD]3dt1[/TD]
[TD]0[/TD]
[TD]3ft1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4at1[/TD]
[TD]4bt1[/TD]
[TD]4ct1[/TD]
[TD]4dt1[/TD]
[TD]4et1[/TD]
[TD]4ft1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5at1[/TD]
[TD]0[/TD]
[TD]5ct1[/TD]
[TD]5dt1[/TD]
[TD]5et1[/TD]
[TD]5ft1[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6at1[/TD]
[TD]6bt1[/TD]
[TD]6ct1[/TD]
[TD]6dt1[/TD]
[TD]6et1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7, align: center"]Table2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1at2[/TD]
[TD]1bt2[/TD]
[TD]1ct2[/TD]
[TD]1dt2[/TD]
[TD]1et2[/TD]
[TD]1ft2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2at2[/TD]
[TD]0[/TD]
[TD]2ct2[/TD]
[TD]0[/TD]
[TD]2et2[/TD]
[TD]2ft2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3at2[/TD]
[TD]3bt2[/TD]
[TD]3ct2[/TD]
[TD]3dt2[/TD]
[TD]0[/TD]
[TD]3ft2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4at2[/TD]
[TD]4bt2[/TD]
[TD]0[/TD]
[TD]4dt2[/TD]
[TD]4et2[/TD]
[TD]4ft2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5at2[/TD]
[TD]5bt2[/TD]
[TD]5ct2[/TD]
[TD]5dt2[/TD]
[TD]5et2[/TD]
[TD]5ft2[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6at2[/TD]
[TD]0[/TD]
[TD]6ct2[/TD]
[TD]6dt2[/TD]
[TD]6et2[/TD]
[TD]6ft2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 7, align: center"]Table3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1at3[/TD]
[TD]1bt3[/TD]
[TD]1ct3[/TD]
[TD]1dt3[/TD]
[TD]0[/TD]
[TD]1ft3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2at3[/TD]
[TD]2bt3[/TD]
[TD]2ct3[/TD]
[TD]2dt3[/TD]
[TD]2et3[/TD]
[TD]2ft3[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3at3[/TD]
[TD]0[/TD]
[TD]3ct3[/TD]
[TD]3dt3[/TD]
[TD]3et3[/TD]
[TD]3ft3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4at3[/TD]
[TD]4bt3[/TD]
[TD]0[/TD]
[TD]4dt3[/TD]
[TD]4et3[/TD]
[TD]4ft3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5at3[/TD]
[TD]5bt3[/TD]
[TD]5ct3[/TD]
[TD]5dt3[/TD]
[TD]5et3[/TD]
[TD]5ft3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6at3[/TD]
[TD]0[/TD]
[TD]6ct3[/TD]
[TD]6dt3[/TD]
[TD]6et3[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Just to give a look of previously mentioned tables. If there is any more data I can help with, please ask.

 

[L. Howard]

You can do it with a few named ranges and a formula like this.

Where:
A8 is a drop down with a list of the Index's named ranges.
A9 is a drop down rows named range (1,2,3,4... down the left side of Index)
A10 ia a drop down of columns named range (a,b,c,d,e... across the top of the Index)

=INDEX(INDIRECT(A8),MATCH(A9,Tb1Row,0),MATCH(A10,Tb1Col,0))

Here is a link to a sheet with a demo.

https://www.dropbox.com/s/r98p4z3aigyvu5z/Index Match x three.xlsm?dl=0

Howard

 

[Friikijs]

Thank you kindly, exactly what I was looking for.

 

[L. Howard]

You're welcome, thanks for the feedback.

Howard