Hello,
I am trying to lookup a value that resides within a specific range of cells (or Index/Match). This range is dynamic and changes in size. The beginning of this specific range starts with a known cell value and ends with another known cell value. The value I am trying to lookup is somewhere inside this specific dynamic range. However, this specific dynamic range also resides inside a larger overall array range that is also dynamic an changes in size. The size of this larger overall array range does not really matter, as long as I can find the smaller specific dynamic range within, and then lookup the value within.
The logic would be as follows:
Look in overall array A:A, and find a specific range that being with a cell containing "16S:CADETL" and ends with a cell containing "16R:CADETL". Then find and lookup cell value "A1" in order to return "YES".
I want only the "A1" within the specific range. There can be many other "A1" values within the larger overall array range, however I only want the "A1" within the smaller specific range. In other words, the "A1" can exist multiple times within the overall array range (or not), but I want only the specific "A1" within the specific range.
Here is an illustration of what I want as a result:
Excel 2010
<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Any help would be greatly appreciated.
Thanks,
ggee
I am trying to lookup a value that resides within a specific range of cells (or Index/Match). This range is dynamic and changes in size. The beginning of this specific range starts with a known cell value and ends with another known cell value. The value I am trying to lookup is somewhere inside this specific dynamic range. However, this specific dynamic range also resides inside a larger overall array range that is also dynamic an changes in size. The size of this larger overall array range does not really matter, as long as I can find the smaller specific dynamic range within, and then lookup the value within.
The logic would be as follows:
Look in overall array A:A, and find a specific range that being with a cell containing "16S:CADETL" and ends with a cell containing "16R:CADETL". Then find and lookup cell value "A1" in order to return "YES".
I want only the "A1" within the specific range. There can be many other "A1" values within the larger overall array range, however I only want the "A1" within the smaller specific range. In other words, the "A1" can exist multiple times within the overall array range (or not), but I want only the specific "A1" within the specific range.
Here is an illustration of what I want as a result:
Excel 2010
A | B | C | D | |
---|---|---|---|---|
1 | Data Codes | Values | ||
2 | 16S:LINK | |||
3 | 10 | X | ||
4 | 11 | X | ||
5 | A1 | NO | not this one | |
6 | 17 | X | ||
7 | A1 | NO | not this one | |
8 | 19 | X | ||
9 | 20 | X | ||
10 | 16R:LINK | |||
11 | 16S:CADETL | |||
12 | 9 | X | ||
13 | 11 | X | ||
14 | A1 | YES | I want this one | |
15 | 14 | X | ||
16 | 16R:CADETL | |||
17 | 16S:OPTN | |||
18 | 10 | X | ||
19 | 12 | X | ||
20 | 15 | X | ||
21 | A1 | NO | not this one | |
22 | 18 | X | ||
23 | 16R:OPTN |
<colgroup><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
Any help would be greatly appreciated.
Thanks,
ggee