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]

	1st variable:	Table2
	2nd variable:	c
	3rd variable:	4
	Result:	4ct2
Table1
	a	b	c	d	e	f
1	1at1	1bt1	1ct1	1dt1	1et1	1ft1
2	2at1	0	2ct1	2dt1	2et1	2ft1
3	3at1	3bt1	0	3dt1	0	3ft1
4	4at1	4bt1	4ct1	4dt1	4et1	4ft1
5	5at1	0	5ct1	5dt1	5et1	5ft1
6	6at1	6bt1	6ct1	6dt1	6et1	0
Table2
	a	b	c	d	e	f
1	1at2	1bt2	1ct2	1dt2	1et2	1ft2
2	2at2	0	2ct2	0	2et2	2ft2
3	3at2	3bt2	3ct2	3dt2	0	3ft2
4	4at2	4bt2	0	4dt2	4et2	4ft2
5	5at2	5bt2	5ct2	5dt2	5et2	5ft2
6	6at2	0	6ct2	6dt2	6et2	6ft2
Table3
	a	b	c	d	e	f
1	1at3	1bt3	1ct3	1dt3	0	1ft3
2	2at3	2bt3	2ct3	2dt3	2et3	2ft3
3	3at3	0	3ct3	3dt3	3et3	3ft3
4	4at3	4bt3	0	4dt3	4et3	4ft3
5	5at3	5bt3	5ct3	5dt3	5et3	5ft3
6	6at3	0	6ct3	6dt3	6et3	0

<tbody>
</tbody>

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