How to Check a column for particular value and use it in a formula in another Cell??

[saranyatl]

Hello

I have a data as shown below.

I need to Check Column B at what value it goes above zero.At C i need to enter the corresponding A Value.


[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]5[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]0.1[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]0.2[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]0.3[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0.4[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]0.5[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]0.6[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl66, width: 64"]A[/TD]
[TD="class: xl66, width: 64"]B[/TD]
[TD="class: xl66, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]7[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]4[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]7[/TD]
[TD="class: xl65"]0.1[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]8[/TD]
[TD="class: xl65"]0.2[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]0.3[/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]0.4[/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

 

[jadhava15]

Hi,

If you A column is sorted in ascending order then you can use below formula.
=MIN(IF(B:B>0,A:A))

use Ctrl+Shift+Enter to enter the formula as this is array formula.

Hope this helps.

Regards,
Amitkumar J.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX($A$1:$A$11,MATCH(1,IF(ISNUMBER($B$1:$B$11),IF($B$1:$B$11>0,1)),0))

 

[saranyatl]

Thank you..It is working perfectly..

 

[Aladin Akyurek]

Thank you..It is working perfectly..

You are welcome.