Need normal formula instead volatile

[motilulla]

Hello,</SPAN>

At present using following volatile formula does it is possible to be converted in the normal formula</SPAN></SPAN>

Book1
	E	F	G
1
2
3	3 | 0 | 0 | 2 | 2 | 2 | 1 | 1 | 1		3
Sheet1

Cell Formulas
Range	Formula
G3	=MATCH(1000,INDEX(FIND(" | "&ROW(INDIRECT("1:999"))&" | "," | "&E3&" | "),0))

Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[jasonb75]

Just remove the volatile function.

=MATCH(1000,INDEX(FIND(" | "&ROW($1:$999)&" | "," | "&E3&" | "),0))

It will work as long as you're not in the habit of deleting rows.

 

[motilulla]

Just remove the volatile function.

=MATCH(1000,INDEX(FIND(" | "&ROW($1:$999)&" | "," | "&E3&" | "),0))

It will work as long as you're not in the habit of deleting rows.

jasonb75, it is running fine thank you very much for your help</SPAN></SPAN>

Kind Regards,</SPAN>
Moti</SPAN>

 

[FormR]

Hi, here's a version that remains robust against row insertion and deletion.

=MATCH(1000,INDEX(FIND(" | "&ROW(INDEX(G:G,1):INDEX(G:G,999))&" | "," | "&E3&" | "),0))

 

[motilulla]

Hi, here's a version that remains robust against row insertion and deletion.

=MATCH(1000,INDEX(FIND(" | "&ROW(INDEX(G:G,1):INDEX(G:G,999))&" | "," | "&E3&" | "),0))

FormR, tried it is also running fine, I noticed the difference in your formula clicking on formula bar this highlights only the column G instead of all area, thank you so much for your help</SPAN></SPAN>

Kind Regards,</SPAN>
Moti</SPAN></SPAN>

 

[jasonb75]

If you're trying to make the formula more efficient then you could remove the array concatenation as well, the formula would still work correctly.

=MATCH(1000,INDEX(FIND(ROW($G$1:$G$999),E3),0))

If your original data only ever contains single digit numbers then you don't need $999 as the end row, $9 will suffice. This is probably the biggest improvement that you could make.

The method suggested by FormR is only needed if you're in the habit of deleting rows, if you don't delete rows then the index union is not necessary.

 

[motilulla]

If you're trying to make the formula more efficient then you could remove the array concatenation as well, the formula would still work correctly.

=MATCH(1000,INDEX(FIND(ROW($G$1:$G$999),E3),0))

If your original data only ever contains single digit numbers then you don't need $999 as the end row, $9 will suffice. This is probably the biggest improvement that you could make.

The method suggested by FormR is only needed if you're in the habit of deleting rows, if you don't delete rows then the index union is not necessary.

jasonb75, really I do not delete any rows, tested your formula rectified $9 it is working well. Thank you for shorting it.</SPAN></SPAN>

Book1
	E	F	G
1
2
3	3 | 0 | 0 | 2 | 2 | 2 | 1 | 1 | 1		3
Sheet1

Cell Formulas
Range	Formula
G3	=MATCH(1000,INDEX(FIND(ROW($G$1:$G$9),E3),0))

Kind Regards,</SPAN>
Moti</SPAN></SPAN>

 

[FormR]

Hi, If it's just single digit numbers you can also hard code them and maintain robustness (which I personally consider to be important, even if you don't intend to insert or delete rows).

=MATCH(1000,INDEX(FIND({1,2,3,4,5,6,7,8,9},E3),0))

 

[motilulla]

Hi, If it's just single digit numbers you can also hard code them and maintain robustness (which I personally consider to be important, even if you don't intend to insert or delete rows).

=MATCH(1000,INDEX(FIND({1,2,3,4,5,6,7,8,9},E3),0))

FormR, I also consider this one is the best solution of my problem!</SPAN></SPAN>

I appreciate your help </SPAN></SPAN>

Kind Regards,</SPAN>
Moti </SPAN>