Lookup multiple cell values in a specific range

[spill-the-beans]

Hello all,

Any help on solving my excel nightmares would be much appreciated. The relevant columns of my dataset look like this:

1	V	W	X	BA
2	50	1	0	51
3	51	0	0
4	52	0	0
5	68	1	0	72
6	69	1	0
7	70	1	0
8	71	1	0
9	72	0	0
10	73	0	0

<tbody>
</tbody>

I need a formula that I can copy down column BA.

Looking in column, V, see that V2 does not equal V1 +1.
Start the search from row 2.
Find the first time W=0 at the same time that x=0.
For that double 0 row number, copy the value in column V and put it in column BA on the row that you started looking in, so BA2.

Looking in column V, V3 = V2+1, so BA3 can be blank. Same with V4 and BA4.

But V5 does not equal V4+1, so the search starts again from row 5.
The first time W=0 and X=0 is row 9.
The number in column V on row 9 is 72, so BA5 should say 72.

 

[Snakehips]

spill-the-beans,

Perhaps try th ARRAY formula ....

Excel 2007

	V	W	X	BA
1
2	50	1	0	51
3	51	0	0
4	52	0	0
5	68	1	0	72
6	69	1	0
7	70	1	0
8	71	1	0
9	72	0	0
10	73	0	0

<COLGROUP><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>

Sheet2​

Array Formulas Cell Formula BA2 {=IF(NOT(V2=V1+1),INDEX(V2:V$10,MATCH(1,(W2:W$10=0)*(X2:X$10=0 ),0)),"")} <THEAD> </THEAD><TBODY> </TBODY> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<TBODY>
</TBODY>

Hope that helps.

 

[spill-the-beans]

Yes, that helped so much - thank you!