LOOKUP 3 tables different size

[GerryZ]

Hello Everybody
As you can see I've 3 table with different size
than in A24, A25, A26 I've 3 dropdown list
I need the expected out come in A28
I want to be able that if I choose table 1, Than Horizzontal size 400, and vertical size 160
The result wil be 388
Just like in the example belowe
Thankyou!

Book1
	A	B	C	D	E	F	G	H	I
1	table1	200	300	400	500	600	700
2	100	138	1132	1004	52	240	776
3	120	175	921	79	149	21	586
4	140	87	188	402	231	192	949
5	160	427	472	388	136	872	463
6	180	175	311	387	774	1080	85
7	table2	100	150	200	250
8	30	1135	1417	978	514
9	60	53	1311	864	78
10	90	629	1545	1019	1509
11	120	452	1003	195	87
12	150	502	1139	1551	670
13	180	300	1343	136	66
14	210	1571	935	571	1107
15	240	459	927	336	698
16	table3	500	1000	1500	2000	2500	3000	3500	4000
17	50	260	379	520	1813	225	1530	1013	1530
18	100	162	786	597	575	651	1745	1845	1741
19	150	1299	1806	151	1994	384	924	619	1769
20	200	1781	1241	161	1869	1686	653	1683	1974
21
22
23
24	table	table1
25	Horizzontal size	400
26	vertical size	160
27
28	Result	388
Foglio1

 

[chicagocomputerclasses]

Try this:
I had an extra empty row on top of my worksheet, so you will need to shift it one.

=INDEX(CHOOSE(RIGHT($B$25,1),$B$3:$G$7,$B$9:$E$16,$B$18:$I$21),MATCH($B$27,CHOOSE(RIGHT($B$25,1),$A$3:$A$7,$A$9:$A$16,$A$18:$A$21),0),MATCH($B$26,CHOOSE(RIGHT($B$25,1),$B$2:$G$2,$B$8:$E$8,$B$17:$I$17),0))

 

[JMKnapp]

You could also try to employ named ranges. Name your first table (A1:G6) "Table1"; your second table (A7:E15) "Table2"; and your third table (A16:I20) "Table3"

Now, in your Result formula in B28, limit that range of your Horizontal and Vertical lookup by the RANGE name in B24.

 

[GerryZ]

@chicagocompuer
your formula works fine, but I made a mistake I should not write TABLE1, table2 and table 3 but the real name of the table, i know thats make the the different because the RIGHT function will be not usefull anymore
this is the real table

Book1
	A	B	C	D	E	F	G	H	I
2	finestra	200	300	400	500	600	700
3	100	138	1132	1004	52	240	776
4	120	175	921	79	149	21	586
5	140	87	188	402	231	192	949
6	160	427	472	388	136	872	463
7	180	175	311	387	774	1080	85
8	porta scorrevole	100	150	200	250
9	30	1135	1417	978	514
10	60	53	1311	864	78
11	90	629	1545	1019	1509
12	120	452	1003	195	87
13	150	502	1139	1551	670
14	180	300	1343	136	66
15	210	1571	935	571	1107
16	240	459	927	336	698
17	tapparelle	500	1000	1500	2000	2500	3000	3500	4000
18	50	260	379	520	1813	225	1530	1013	1530
19	100	162	786	597	575	651	1745	1845	1741
20	150	1299	1806	151	1994	384	924	619	1769
21	200	1781	1241	161	1869	1686	653	1683	1974
22
23
24
25	table	finestra
26	Horizzontal size	200
27	vertical size	180
28
29	Result	#VALORE!
Foglio1

Cell Formulas
Range	Formula
B29	=INDEX(CHOOSE(RIGHT($B$25,1),$B$3:$G$7,$B$9:$E$16,$B$18:$I$21),MATCH($B$27,CHOOSE(RIGHT($B$25,1),$A$3:$A$7,$A$9:$A$16,$A$18:$A$21),0),MATCH($B$26,CHOOSE(RIGHT($B$25,1),$B$2:$G$2,$B$8:$E$8,$B$17:$I$17),0))

 

[azumi]

=IF(B24="table1",INDEX(B2:G6,MATCH(B26,A2:A6,0),MATCH(B25,B1:G1,0)),IF(B24="table2",INDEX(B8:E15,MATCH(B26,A8:A15,0),MATCH(B25,B7:E7,0)),INDEX(B17:I20,MATCH(B26,A17:A20,0),MATCH(B25,B16:I16,0))))

 

[GerryZ]

@Azumi
Your formula Works fine, but the problem is that I' ve 23 different tables, and your formula becaomes very very long
do you belive is there a better solution?

 

[GerryZ]

@JMKnapp

I don't know what you mean, can you post a sample?
because would be interesting what you are sayng but I don't know how to apply
thank you!

 

[chicagocomputerclasses]

replace right function with this

SUMPRODUCT(--NOT(ISNUMBER(INDIRECT("A1:A"&MATCH(B25,A:A)))))

 

[GerryZ]

Thank You Chicagocomputerclasses and All
I will use Chicagocomputerclasses formula because is the shorter one
Thank you

 

[chicagocomputerclasses]

This could be a little too much, but I've figured out a way to do this with one large table instead of breaking it down into 3 separate tables.
This way you can keep adding as many tables as you want, you just have to make sure to update range end limits.

=INDEX(A1:I21,MATCH(B26,INDIRECT("A"&MATCH(B24,A1:A21,0)&":A"&MATCH(B24,A1:A21,0)+MATCH(1,TRANSPOSE(--NOT(ISNUMBER(INDIRECT("A"&MATCH(B24,A1:A20,0)+1&":A21")))),0)-1),0)+MATCH(B24,A1:A21,0)-1,MATCH(B25,INDIRECT(MATCH(B24,A1:A21,0)&":"&MATCH(B24,A1:A21,0)),0))