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

jeffdlinn

New Member
Joined
Nov 18, 2013
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Type:Complete
2Range:5
3Area:East
4Result:136
5
6Complete
7Range FromRange ToNorthEastWest
815104136140
9610104156161
101115120177181
111620137211216
122125152246251
132630155250254
143135186286291
153640186299303
164145215317321
174650215337342
18
19Modified
20Range FromRange ToNorthEastWest
2115114147152
22610115179183
231115144200204
241620160234238
252125175281285
262630178284289
273135221321325
283640221333337
294145250362367
304650250383388
Sheet1
Cell Formulas
RangeFormula
B4=INDEX(C8:E17,MATCH(B2,B8:B17),MATCH(B3,C7:E7))
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
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.
 
Last edited:
Upvote 0
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))
 
Upvote 0
Another option...
=INDEX((C8:E17,C21:D30),MATCH(B2,B8:B17),MATCH(B3,C7:E7),MATCH(B1,{"complete","modified"},0))
 
Upvote 0
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.
 
Upvote 0
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...
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][td]
H​
[/td][/tr]
[tr][td]
6​
[/td][td][/td][td]
[/td][td]Complete[/td][td]
[/td][td]
[/td][td]Modified[/td][td][/td][td][/td][/tr]

[tr][td]
7​
[/td][td]Range From[/td][td]Range To[/td][td]North[/td][td]East[/td][td]West[/td][td]North[/td][td]East[/td][td]West[/td][/tr]

[tr][td]
8​
[/td][td]
1​
[/td][td]
5​
[/td][td]
104​
[/td][td]
136​
[/td][td]
140​
[/td][td]
114​
[/td][td]
147​
[/td][td]
152​
[/td][/tr]

[tr][td]
9​
[/td][td]
6​
[/td][td]
10​
[/td][td]
104​
[/td][td]
156​
[/td][td]
161​
[/td][td]
115​
[/td][td]
179​
[/td][td]
183​
[/td][/tr]

[tr][td]
10​
[/td][td]
11​
[/td][td]
15​
[/td][td]
120​
[/td][td]
177​
[/td][td]
181​
[/td][td]
144​
[/td][td]
200​
[/td][td]
204​
[/td][/tr]

[tr][td]
11​
[/td][td]
16​
[/td][td]
20​
[/td][td]
137​
[/td][td]
211​
[/td][td]
216​
[/td][td]
160​
[/td][td]
234​
[/td][td]
238​
[/td][/tr]

[tr][td]
12​
[/td][td]
21​
[/td][td]
25​
[/td][td]
152​
[/td][td]
246​
[/td][td]
251​
[/td][td]
175​
[/td][td]
281​
[/td][td]
285​
[/td][/tr]

[tr][td]
13​
[/td][td]
26​
[/td][td]
30​
[/td][td]
155​
[/td][td]
250​
[/td][td]
254​
[/td][td]
178​
[/td][td]
284​
[/td][td]
289​
[/td][/tr]

[tr][td]
14​
[/td][td]
31​
[/td][td]
35​
[/td][td]
186​
[/td][td]
286​
[/td][td]
291​
[/td][td]
221​
[/td][td]
321​
[/td][td]
325​
[/td][/tr]

[tr][td]
15​
[/td][td]
36​
[/td][td]
40​
[/td][td]
186​
[/td][td]
299​
[/td][td]
303​
[/td][td]
221​
[/td][td]
333​
[/td][td]
337​
[/td][/tr]

[tr][td]
16​
[/td][td]
41​
[/td][td]
45​
[/td][td]
215​
[/td][td]
317​
[/td][td]
321​
[/td][td]
250​
[/td][td]
362​
[/td][td]
367​
[/td][/tr]

[tr][td]
17​
[/td][td]
46​
[/td][td]
50​
[/td][td]
215​
[/td][td]
337​
[/td][td]
342​
[/td][td]
250​
[/td][td]
383​
[/td][td]
388​
[/td][/tr]
[/table]

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

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top