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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]BA[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]50[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]51[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]51[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]52[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]68[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]72[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]69[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]70[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]71[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]72[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]73[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

<COLGROUP><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="bgcolor: #ffff00, align: right"][/TD]
[TD="bgcolor: #ffff00, align: right"][/TD]
[TD="bgcolor: #ffff00, align: right"][/TD]
[TD="bgcolor: #ffff00, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]51[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]72[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]69[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]71[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]72[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</TBODY>

Sheet2​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<THEAD>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</THEAD><TBODY>[TR]
[TH="width: 10, bgcolor: #e0e0f0"]BA2[/TH]
[TD="align: left"]{=IF(NOT(V2=V1+1),INDEX(V2:V$10,MATCH(1,(W2:W$10=0)*(X2:X$10=0 ),0)),"")}[/TD]
[/TR]
</TBODY>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</TBODY>[/TABLE]



Hope that helps.

 

[spill-the-beans]

Yes, that helped so much - thank you!