Formula using index and match to find smallest/minimum value > zero

[already999]

Hi,
I have the following formula to find the max and min value in a column:

=(MAX(Data!B:B)&" - "&MIN(IF(Data!B:B>0;(Data!B:B))))

But I want to find the corresponding cell in another column using index and match. But I can’t figure it out to find the smallest value greater than zero in this formula

=INDEX(Data!A:A;MATCH(MIN(Data!B:B);Data!B:B;0))

Thanks in advance for your help

 

[etaf]

as you are using 2010 , you can use MINIFS

try
=INDEX(A1:A1000,MATCH(SMALL(B1:B1000,COUNTIF(B1:B1000,0)+1),B1:B1000,0))

Book1
	A	B	C
1	date	order
2	2/1/25	0	2/4/25
3	2/2/25	12
4	2/3/25	0
5	2/4/25	6
6
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX(A1:A1000,MATCH(SMALL(B1:B1000,COUNTIF(B1:B1000,0)+1),B1:B1000,0))

for reference - finding smallest value ignoring zero

How to find the minimum value in range while ignoring 0 value in Microsoft Excel

To find the minimum numerical value in range while ignoring the 0 value, we can use the Small function along with Countif function in Microsoft Excel. SMALL: - This function is used to return the kth smallest value in a … Continue reading →

www.exceltip.com

 

[already999]

as you are using 2010 , you can use MINIFS

try
=INDEX(A1:A1000,MATCH(SMALL(B1:B1000,COUNTIF(B1:B1000,0)+1),B1:B1000,0))

Book1
	A	B	C
1	date	order
2	2/1/25	0	2/4/25
3	2/2/25	12
4	2/3/25	0
5	2/4/25	6
6
Sheet1

Cell Formulas
Range	Formula
C2	C2	=INDEX(A1:A1000,MATCH(SMALL(B1:B1000,COUNTIF(B1:B1000,0)+1),B1:B1000,0))

for reference - finding smallest value ignoring zero

How to find the minimum value in range while ignoring 0 value in Microsoft Excel

To find the minimum numerical value in range while ignoring the 0 value, we can use the Small function along with Countif function in Microsoft Excel. SMALL: - This function is used to return the kth smallest value in a … Continue reading →

www.exceltip.com

Hi Etaf,

Thanks for your suggestion but if I'm right there is no MINIFS formula in version 2010.

Kr.

 

[etaf]

ok hence why i used small and count

should have said CANT, missed the T out - sorry

 

[Peter_SSs]

Welcome to the MrExcel board!

I don't know if it is possible with your data but the post #2 formula would return an incorrect result if negative values can also exist in column B.
That formula could be adapted to account for such circumstances as shown in D2 below and another option is given in C2.

25 02 19.xlsm
	A	B	C	D
1	date	order
2	1/02/2025	0	3/02/2025	3/02/2025
3	2/02/2025	12
4	3/02/2025	2
5	4/02/2025	-6
already999

Cell Formulas
Range	Formula
C2	C2	=INDEX(A2:A1000,MATCH(AGGREGATE(15,6,B2:B1000/(B2:B1000>0),1),B2:B1000,0))
D2	D2	=INDEX(A1:A1000,MATCH(SMALL(B1:B1000,COUNTIF(B1:B1000,"<=0")+1),B1:B1000,0))

 

[already999]

ok hence why i used small and count

should have said CANT, missed the T out - sorry

Thanks etaf, working fine with the update from Peter_SSs concerning negative values.. Kr Al

 

[already999]

Welcome to the MrExcel board!

I don't know if it is possible with your data but the post #2 formula would return an incorrect result if negative values can also exist in column B.
That formula could be adapted to account for such circumstances as shown in D2 below and another option is given in C2.

25 02 19.xlsm
	A	B	C	D
1	date	order
2	1/02/2025	0	3/02/2025	3/02/2025
3	2/02/2025	12
4	3/02/2025	2
5	4/02/2025	-6
already999

Cell Formulas
Range	Formula
C2	C2	=INDEX(A2:A1000,MATCH(AGGREGATE(15,6,B2:B1000/(B2:B1000>0),1),B2:B1000,0))
D2	D2	=INDEX(A1:A1000,MATCH(SMALL(B1:B1000,COUNTIF(B1:B1000,"<=0")+1),B1:B1000,0))

Thnks for this top update Peter_SSs. Kr Al

 

[etaf]

you are welcome, and glad people like Peter+SSs are around to check on things and be able to check and correct anything overlooked

 

[Peter_SSs]

You're welcome. Glad to help. Thanks for the follow-up.