Excel Index - how to return a blank if the cells show 0-CodePudding

Excel snip

In column P I've entered a formala to return the name of the column with the lowest value J to N.

The formula is:

=INDEX($J$1:$N$1,MATCH(MIN(J2:N2),J2:N2,0))

For row 5 to 9 all the values are zero so I'd like P5 to P9 to return a blank cell. Instead it's returning the name of column J. The values in J to N are formulas multiplying D with (hidden) E to I.

I have tried using various IF functions but I can't get it to work without giving me an error popup

CodePudding user response：

Perhaps you could try something along the lines of using an IF() logic

• Formula used in cell P2

=IF(O2=0,"",INDEX($J$1:$N$1,MATCH(O2,J2:N2,0)))

Alternatively, if you are an MS365 user then can try using BYROW()

• Formula used in cell P2

=BYROW(J2:N9,LAMBDA(m,IF(SUM(m)=0,"",XLOOKUP(MIN(m),m,$J$1:$N$1))))