excel formula - dynamic range based on cell value

[RCBricker]

I am sure that this is easy, but I just cant get it.

I have a data set that I need to create different arrays based on values in the data set.

Value 1 is our starting point of our array.

Value 1 is a variable that I have created a helper column in Column H which will have "Start" in the cell next to the variable I am using for that run. For this example, Start appears next to any cell in column G in which "D" appears.

Value 2 is one of two possible ending points of the array.

Value two in the example is "XXX". IF value 2 exists then it should be used to determine the last row in the index array range.

value 3 is the second of two possible ending points to the array.

If Value 2 does not exist then use value 3 which is "YYY".


Last caveat is that the array should look only down the column at values before the next instance of value 1 (in this example D).

example starts on G4 with G3 as a header

so if G4=A, then G7="D" our value 1, G20 ="XXX" our value 2 and G26 ="YYY" our value 3. Value 1 is again found in G32 and there is no value 2, but value 3 is found in G45

As such, the index array should be G7:G20 in the first instance and G32:G45 in the second instance.

Column G
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
XXX
Q
R
S
T
U
YYY
*is blank
*is blank
A
B
C
D
...
YYY

any help would be greatly appreciated.

thanks,

Rich

 

[RCBricker]

Here is my total solution case anyone was following the three threads

=IF(H7="Start",INDEX(INDIRECT("$G$"&ROW()&":$G$"&IF(ISERROR(ROW(INDEX(INDIRECT("$G$"&ROW()&":$G$1048576"),MATCH("XXX",INDIRECT("$G$"&ROW()&":$G$1048576"),0)))),ROW(INDEX(INDIRECT("$G$"&ROW()&":$G$1048576"),MATCH("YYY",INDIRECT("$G$"&ROW()&":$G$1048576"),0))),ROW(INDEX(INDIRECT("$G$"&ROW()&":$G$1048576"),MATCH("XXX",INDIRECT("$G$"&ROW()&":$G$1048576"),0))))),4,1),"")