Return an index number in the range

[Mike7]

Hi.
I have different numbers in the range A1:T1 "12 4 43 23 18 19 37 32 21 7 40 25 41 22 9 11 33 15 29 46" I need a formula for W1 to find an index number of the first occurrence of the value <10 from right to left in the range A1:T1. In this case the value in W1 must be 6.
Also I need a similar formula for the first occurrence of the value between two numbers. In case <40 >30 the value in W1 must be 4.

Thanks.

 

[S M C]

=count($a$1:$t$1)-sumproduct(max(($a$1:$t$1<10)*(column($a$1:$t$1)-column($a$1)+1)))+1

=count($a$1:$t$1)-sumproduct(max(($a$1:$t$1<40)*($a$1:$t$1>30)*(column($a$1:$t$1)-column($a$1)+1)))+1

 

[markmzz]

Or try this (array formula use Ctrl+Shift+Enter and not only Enter):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">12</td><td style="text-align: center;;">9</td><td style="text-align: center;;">11</td><td style="text-align: center;;">33</td><td style="text-align: center;;">15</td><td style="text-align: center;;">29</td><td style="text-align: center;;">46</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Number</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">U1</th><td style="text-align:left">{=COLUMN(<font color="Blue"></font>)-MAX(<font color="Blue">(<font color="Red">A1:T1<10</font>)*COLUMN(<font color="Red">A:T</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[Mike7]

Thanks, S M C. Both work good.
markmzz's works too but only in the next to the range cell.

Thanks!

 

[T. Valko]

Hi.
I have different numbers in the range A1:T1 "12 4 43 23 18 19 37 32 21 7 40 25 41 22 9 11 33 15 29 46" I need a formula for W1 to find an index number of the first occurrence of the value <10 from right to left in the range A1:T1. In this case the value in W1 must be 6.
Also I need a similar formula for the first occurrence of the value between two numbers. In case <40 >30 the value in W1 must be 4.

Thanks.

Here's another one...

=21-LOOKUP(2,1/(A1:T1<10),COLUMN(A1:T1)-COLUMN(A1)+1)

If there are no numbers <10 then you'll get an error.

 

[T. Valko]

Here's another one...

=21-LOOKUP(2,1/(A1:T1<10),COLUMN(A1:T1)-COLUMN(A1)+1)

If there are no numbers <10 then you'll get an error.

Ooops!

I missed your second question:

Also I need a similar formula for the first occurrence of the value between two numbers. In case <40 >30 the value in W1 must be 4.

=21-LOOKUP(2,1/((A1:T1>30)*(A1:T1<40)),COLUMN(A1:T1)-COLUMN(A1)+1)

 

[Mike7]

Both work good too
Thanks T. Valko!

 

[T. Valko]

Both work good too
Thanks T. Valko!

You're welcome. Thanks for the feedback!

 

[markmzz]

Thanks, S M C. Both work good.
markmzz's works too but only in the next to the range cell.

Thanks!

Mike7,

No problem. Look at this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th><th>U</th><th>V</th><th>W</th><th>X</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">12</td><td style="text-align: center;;">9</td><td style="text-align: center;;">11</td><td style="text-align: center;;">33</td><td style="text-align: center;;">15</td><td style="text-align: center;;">29</td><td style="text-align: center;;">46</td><td style="text-align: right;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td><td style="text-align: center;;">****</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Number</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">V1</th><td style="text-align:left">{=COLUMN(<font color="Blue">$U:$U</font>)-MAX(<font color="Blue">(<font color="Red">$A$1:$T$1<10</font>)*COLUMN(<font color="Red">$A:$T</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
Markmzz

 

[Mike7]

Mike7,

No problem. Look at this:

Now it works in every cell, thanks.