SOLVED

Need help with =IFerror function

Copper Contributor

Hi, I would like to ask how do I add IFERROR in this situation?

I have been spending 1 hour on this one since one of the column have #N/A and im trying to make it as a blank.

 

=IF(VLOOKUP(B4,'Planning S15'!A:AT,2,0)>0,(IF(LEN(VLOOKUP(B4,'Planning S15'!A:AT,2,0))=0,"",(VLOOKUP(B4,'Planning S15'!A:AT,2,0)))),(IF(LEN(VLOOKUP(B4,'Planning SM'!A:N,2,0))=0,"",(VLOOKUP(B4,'Planning SM'!A:N,2,0)))))

 

Thanks

2 Replies
best response confirmed by Leon_Redclifft (Copper Contributor)
Solution

@Leon_Redclifft Hard to answer without knowing the goal of the formula. BUt I'd guess this should suffice:

=IFERROR(IF(VLOOKUP(B4,'Planning S15'!A:AT,2,0)>0,(IF(LEN(VLOOKUP(B4,'Planning S15'!A:AT,2,0))=0,"",(VLOOKUP(B4,'Planning S15'!A:AT,2,0)))),(IF(LEN(VLOOKUP(B4,'Planning SM'!A:N,2,0))=0,"",(VLOOKUP(B4,'Planning SM'!A:N,2,0))))),"")

@Leon_Redclifft 

The logic of your formula appears to be

= IF( result1>0,
       IF( LEN(result1)=0,
            "",
            result1
      ),
      IF( LEN(result2)=0,
            "",
            result2)
   )

If that is correct, an error in the first lookup will return #N/A and the second lookup will never happen.  If, instead of testing for a positive result, you test for 'result1' being a number, an error in the first VLOOKUP will cause the second to be evaluated:

= IF( ISNUMBER(result1),
      result1,
      IF(LEN(result2)=0,
           "",
           result2))

I have removed the test on the length of 'result1' because a number cannot have zero length.  I also removed surplus pairs of parentheses.

 

1 best response

Accepted Solutions
best response confirmed by Leon_Redclifft (Copper Contributor)
Solution

@Leon_Redclifft Hard to answer without knowing the goal of the formula. BUt I'd guess this should suffice:

=IFERROR(IF(VLOOKUP(B4,'Planning S15'!A:AT,2,0)>0,(IF(LEN(VLOOKUP(B4,'Planning S15'!A:AT,2,0))=0,"",(VLOOKUP(B4,'Planning S15'!A:AT,2,0)))),(IF(LEN(VLOOKUP(B4,'Planning SM'!A:N,2,0))=0,"",(VLOOKUP(B4,'Planning SM'!A:N,2,0))))),"")

View solution in original post