Index, Match, Match to include a range and multiple tables

[jeffdlinn]

With the help of this forum I now have a index. match, match formula I need to expand upon. Based on the input in cell B2 I need the formula to select a value based on the range in Columns A and B. Additionally, the input in cell B1 will direct the formula to appropriate table (in this case Complete or Modified).

To clarify in this example if cell B1 was Modified (or another table reference), cell B2 was 23 the value in cell B4 would be 281.

Any ideas?

Book1
	A	B	C	D	E
1	Type:	Complete
2	Range:	5
3	Area:	East
4	Result:	136
5
6	Complete
7	Range From	Range To	North	East	West
8	1	5	104	136	140
9	6	10	104	156	161
10	11	15	120	177	181
11	16	20	137	211	216
12	21	25	152	246	251
13	26	30	155	250	254
14	31	35	186	286	291
15	36	40	186	299	303
16	41	45	215	317	321
17	46	50	215	337	342
18
19	Modified
20	Range From	Range To	North	East	West
21	1	5	114	147	152
22	6	10	115	179	183
23	11	15	144	200	204
24	16	20	160	234	238
25	21	25	175	281	285
26	26	30	178	284	289
27	31	35	221	321	325
28	36	40	221	333	337
29	41	45	250	362	367
30	46	50	250	383	388
Sheet1

Cell Formulas
Range	Formula
B4	=INDEX(C8:E17,MATCH(B2,B8:B17),MATCH(B3,C7:E7))

 

[Marcelo Branco]

Maybe this...

Create named ranges
Complete ---> A7:E17
Modified ---> A20:E30

Then try in B4
=INDEX(INDIRECT(B1),MATCH(B2,INDEX(INDIRECT(B1),0,1)),MATCH(B3,INDEX(INDIRECT(B1),1,0),0))

Hope this helps

M.

 

[FDibbins]

Late to the party, but that was the way I went, too (named ranges)...
=INDEX(INDIRECT($B$1),MATCH(B2,B8:B17),MATCH(B3,C7:E7))

 

[FDibbins]

Another option...
=INDEX((C8:E17,C21:D30),MATCH(B2,B8:B17),MATCH(B3,C7:E7),MATCH(B1,{"complete","modified"},0))

 

[Marcelo Branco]

Late to the party, but that was the way I went, too (named ranges)...
=INDEX(INDIRECT($B$1),MATCH(B2,B8:B17),MATCH(B3,C7:E7))

Hi Ford

If in all tables the values in columns 'Range From' and 'Range To' are identical and the regions are the same your simpler formula should work.
Like it.

M.

 

[FDibbins]

Hi Ford

If in all tables the values in columns 'Range From' and 'Range To' are identical and the regions are the same your simpler formula should work.
Like it.

M.

Thanks I based it on the sample provided, which showed the same format. Perhaps another option - IF the tables are all identical - would be to have them side-by-side, then just use...

	A​	B​	C​	D​	E​	F​	G​	H​
6​		​	Complete	​	​	Modified
7​	Range From	Range To	North	East	West	North	East	West
8​	1​	5​	104​	136​	140​	114​	147​	152​
9​	6​	10​	104​	156​	161​	115​	179​	183​
10​	11​	15​	120​	177​	181​	144​	200​	204​
11​	16​	20​	137​	211​	216​	160​	234​	238​
12​	21​	25​	152​	246​	251​	175​	281​	285​
13​	26​	30​	155​	250​	254​	178​	284​	289​
14​	31​	35​	186​	286​	291​	221​	321​	325​
15​	36​	40​	186​	299​	303​	221​	333​	337​
16​	41​	45​	215​	317​	321​	250​	362​	367​
17​	46​	50​	215​	337​	342​	250​	383​	388​

=INDEX(C8:H17,MATCH(B2,B8:B17),MATCH(B3,C7:E7)+MATCH(B1,C6:H6,0)-1)