Forum Discussion

Leon_Redclifft's avatar
Leon_Redclifft
Copper Contributor
Feb 24, 2020
Solved

Need help with =IFerror function

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

excel
Formulas and Functions
  • JKPieterse's avatar
    JKPieterse
    Feb 24, 2020

    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))))),"")

2 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Feb 24, 2020

    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.

     

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Feb 24, 2020

    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))))),"")

Resources