How to use MIN function whilst omitting 0 value within INDEX and MATCH formula

[Dan23]

Gday,

I need to find the lowest number from a range of numbers omitting the value of '0' whilst using the MATCH and INDEX functions within the same formula. Have tried this combination but receive an error.

=INDEX(D36:BXW36,MATCH(MIN(if(D38:BXW38>0,d38:bxw38),D38:BXW38,0))
=INDEX(D36:BXW36,MATCH(MINifs(D38:BXW38,">0"),D38:BXW38,0))

I use EXCEL 2013.

 

[shaowu459]

for your reference

Book1
	A	B	C	D	E	F
1	A	B	C	D	E	F
2	1	2	0	3	0.5	4
3
4		E
Sheet1

Cell Formulas
Range	Formula
B4	B4	=INDEX(A1:F1,MATCH(MIN(IF(A2:F2>0,A2:F2)),IF(A2:F2>0,A2:F2),))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[shaowu459]

LOOKUP is also OK for the case

Book1
	A	B	C	D	E	F
1	A	B	C	D	E	F
2	1	2	0	3	0.5	6
3
4		E
Sheet1

Cell Formulas
Range	Formula
B4	B4	=LOOKUP(1,0/(A2:F2=MIN(IF(A2:F2>0,A2:F2))),A1:F1)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[KRice]

Here is another option that doesn't use an array formula:

MrExcel20200516_b.xlsx
	C	D	E	F	G	H	I	J	K	L
5
6		0.5	0.6	0.7	0.8	0.9	1	1.1	1.2	1.3
7	1.1
8		2.5	2.4	2.8	3.2	0	0	0.8	1	1.9
Sheet6

Cell Formulas
Range	Formula
C7	C7	=INDEX(D6:L6,MATCH(AGGREGATE(15,6,(D8:L8)/(D8:L8>0),1),D8:L8,0))

 

[Dan23]

Thanks, all solutions worked great, extremelly thankful for your time and help! Sorry for late reply, forgot to check my junk email.