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?
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)) |