Nested IF Returns FALSE

Really can't see where i've got the syntax wrong here, please help:


=IF($E17<>"",IF(MOD(F$6,INDEX($D$17:$D$18,MATCH($E17,$B$17:$B$18,)))=0,"int",IF(MOD(F$6,$D17)=0,"x","")))

When $E17 is not blank then it works fine, but when $E17 is blank it returns FALSE, when it should be "".

I have attached a sample spreadsheet

Hi 77highland,

You've omitted the 'value if false' part of the syntax from your second IF statement.

Try:

=IF($E17<>"",IF(MOD(F$6,INDEX($D$17:$D$18,MATCH($E17,$B$17:$B$18,)))=0,"int",IF(MOD(F$6,$D17)=0,"x",""),""))

Regards,

Snook

Hi Snook,

Thank you for the super quick reply but unfortunately I had already tried your suggestion and it returns "you've entered too many arguments for this function"

Hmmm, how about this?

=IF($E17<>"",IF(MOD(F$6,INDEX($D$17:$D$18,MATCH($E17,$B$17:$B$18,)))=0,"int",IF(MOD(F$6,$D17)=0,"x","")),"")

Unfortunately that returns nothing when $E17 is blank. Could it be due to the INDEX/MATCH not being able to return anything and therefore in theory returning an N/A?

Originally Posted by 77highland

but when $E17 is blank it returns FALSE, when it should be "".

I thought that was the result you were expecting?

Unfortunately that returns nothing when $E17 is blank.

But that's what you said you wanted.

Sorry.... it returns "" for all cells. Some cells should return the true statement of "x"......... as per the attached example sheet

Are you talking about the formula in F8 or F9, as there is nothing in row 17?

My apologies, I was trying to protect sensitive data.......Please find attached an updated example sheet.

Now referencing the formula at issue in row 9:

=IF($E9<>"",IF(MOD(F$6,INDEX($D$8:$D$9,MATCH($E9,$B$8:$B$9,)))=0,"int",IF(MOD(F$6,$D9)=0,"x","")))

Why have you changed the formula in F9?
It looks as though the formula you had previously worked & just needed a tweak to get rid of the FALSE.

This was a tricky one to explain and I appreciate your help, but I have managed to figure it out Essentially I needed to include an additional If(MOD(x,x)=0 argument as the final false statement

If interested I have attached an updated example sheet.

Formula in F8 = =IF($E8<>"",IF(MOD(F$6,INDEX($D$8:$D$9,MATCH($E8,$B$8:$B$9,)))=0,"int",IF(MOD(F$6,$D8)=0,"x","")),IF(MOD(F$6,$D8)=0,"x",""))
Formula in F9 = =IF($E9<>"",IF(MOD(F$6,INDEX($D$8:$D$9,MATCH($E9,$B$8:$B$9,)))=0,"int",IF(MOD(F$6,$D9)=0,"x","")),IF(MOD(F$6,$D9)=0,"x",""))

Glad you were able to sort it & thanks for the feedback.