Return index for the smallest positive number in column

[smide]

Hello.


In both columns A and B (A1:A600, B1:B600) I have numbers/indexes in ascending order.
Column A range is from zero up to a 599. Column B could contain negative numbers.


I need to return index from column A for the smallest positive number from column B. Result should be placed in cell C1.


Zero should be treated as negative number in formula.
Column B could repeat the same numbers and it that case return index for the first number.


example.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-12[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]-5[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]-1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

explanation: One (cells B5 and B6) is the smallest positive number in column B. Result in C1 should be 4 because it's a corresponding index from column A (cell A5).

 

[FormR]

Hi, here is one option.

Excel 2013/2016
	A	B	C
1	0	-12	4
2	1	-5
3	2	-1
4	3	0
5	4	1
6	5	1
7	6	2
8	7	3
9	8	3
Sheet1

Cell Formulas
Range	Formula
C1	=INDEX(A1:A600,MATCH(AGGREGATE(15,6,B1:B600/(B1:B600>0),1),B1:B600,0))

 

[smide]

Sorry, I forgot to mention, I'm using excel 2007, so aggregate function is not an option for me...
Do you have any idea what can I use instead of 'aggregate' function?

 

[FormR]

Do you have any idea what can I use instead of 'aggregate' function?

Hi, take note of the array entry instructions.

Excel 2013/2016
	A	B	C
1	0	-12	4
2	1	-5
3	2	-1
4	3	0
5	4	1
6	5	1
7	6	2
8	7	3
9	8	3
Sheet1

Cell Formulas
Range	Formula
C1	{=INDEX(A1:A600,MATCH(MIN(IF(B1:B600>0,B1:B600)),B1:B600,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[smide]

It's ok now. Works great. Thank you.

 

[Peter_SSs]

If you would prefer a normal-entry formula then you could see if either of these suit.

The C1 formula takes your statement "Column A range is from zero up to a 599" literally.
If the values in column A may not start at zero or continually increment by 1 then try the C2 formula.

Both formula rely on your statement about ascending order: "(A1:A600, B1:B600) .. have numbers/indexes in ascending order."

Excel Workbook
	A	B	C
1	0	-12	4
2	1	-5	4
3	2	-1
4	3	0
5	4	1
6	5	1
7	6	2
8	7	3
9	8	3
Index