Show text if formula result = 0

[sharky12345]

I've got the following formula which works fine, but I need it to show the text "NIL FOUND" if the columns checked result in a formula result of zero, (i.e. no maximum value).

Can anyone help?

=IFERROR(INDEX($N$7:$Q$7,0,MATCH(MAX($N7:$Q8),$N8:$Q8,0)),"")

 

[Dryver14]

Not tried but will it not go in an if statement

=IF(FERROR(INDEX($N$7:$Q$7,0,MATCH(MAX($N7:$Q8),$N8:$Q8,0)),"") =0,"NIL FOUND",

IFERROR(INDEX($N$7:$Q$7,0,MATCH(MAX($N7:$Q8),$N8:$Q8,0)),"") )​

​

<strike></strike>

 

[sharky12345]

That doesn't work I'm afraid - it produces '#NAME?'

Not sure if this makes a difference but I can explain a little further - I need it to show that text if there is no 'MAX' value because all of the evaluated cells are zero.

 

[snjpverma]

sharky,

the error is due to the spelling mistake, see this

=IF(IFERROR(INDEX($N$7:$Q$7,0,MATCH(MAX($N7:$Q8),$N8:$Q8,0)),"") =0,"NIL FOUND",

IFERROR(INDEX($N$7:$Q$7,0,MATCH(MAX($N7:$Q8),$N8:$Q8,0)),"") )​

 

[Aladin Akyurek]

I've got the following formula which works fine, but I need it to show the text "NIL FOUND" if the columns checked result in a formula result of zero, (i.e. no maximum value).

Can anyone help?

1. A max value can have multiple instances. The formula you have can just produce a result for the first instance it comes across.

2. That the max value must be above 0, try...

=IF(ISNUMBER(1/MAX($N8:$Q8)),INDEX($N$7:$Q$7,MATCH(MAX(

$N8:$Q8​

),

$N8:$Q8​

,0)),"NIL FOUND")

Note the correction involving the max range.

 

[snjpverma]

Try this

=IFERROR(1/(1/INDEX($N$7:$Q$7,0,MATCH(MAX($N7:$Q8),$N8:$Q8,0))),"Nil Found")

 

[sharky12345]

Guys - got it working, thanks to all who have taken the trouble to reply!