SOLVED

Need help with =IFerror function

%3CLINGO-SUB%20id%3D%22lingo-sub-1190246%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20%3DIFerror%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190246%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20would%20like%20to%20ask%20how%20do%20I%20add%20IFERROR%20in%20this%20situation%3F%3C%2FP%3E%3CP%3EI%20have%20been%20spending%201%20hour%20on%20this%20one%20since%20one%20of%20the%20column%20have%20%23N%2FA%20and%20im%20trying%20to%20make%20it%20as%20a%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(VLOOKUP(B4%2C'Planning%20S15'!A%3AAT%2C2%2C0)%26gt%3B0%2C(IF(LEN(VLOOKUP(B4%2C'Planning%20S15'!A%3AAT%2C2%2C0))%3D0%2C%22%22%2C(VLOOKUP(B4%2C'Planning%20S15'!A%3AAT%2C2%2C0))))%2C(IF(LEN(VLOOKUP(B4%2C'Planning%20SM'!A%3AN%2C2%2C0))%3D0%2C%22%22%2C(VLOOKUP(B4%2C'Planning%20SM'!A%3AN%2C2%2C0)))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1190246%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1190322%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20%3DIFerror%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190322%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F546705%22%20target%3D%22_blank%22%3E%40Leon_Redclifft%3C%2FA%3E%20Hard%20to%20answer%20without%20knowing%20the%20goal%20of%20the%20formula.%20BUt%20I'd%20guess%20this%20should%20suffice%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(IF(VLOOKUP(B4%2C'Planning%20S15'!A%3AAT%2C2%2C0)%26gt%3B0%2C(IF(LEN(VLOOKUP(B4%2C'Planning%20S15'!A%3AAT%2C2%2C0))%3D0%2C%22%22%2C(VLOOKUP(B4%2C'Planning%20S15'!A%3AAT%2C2%2C0))))%2C(IF(LEN(VLOOKUP(B4%2C'Planning%20SM'!A%3AN%2C2%2C0))%3D0%2C%22%22%2C(VLOOKUP(B4%2C'Planning%20SM'!A%3AN%2C2%2C0)))))%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1190416%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20%3DIFerror%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1190416%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F546705%22%20target%3D%22_blank%22%3E%40Leon_Redclifft%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20logic%20of%20your%20formula%20appears%20to%20be%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%20IF(%20result1%26gt%3B0%2C%0A%20%20%20%20%20%20%20IF(%20LEN(result1)%3D0%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20result1%0A%20%20%20%20%20%20)%2C%0A%20%20%20%20%20%20IF(%20LEN(result2)%3D0%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%22%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20result2)%0A%20%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EIf%20that%20is%20correct%2C%20an%20error%20in%20the%20first%20lookup%20will%20return%20%23N%2FA%20and%20the%20second%20lookup%20will%20never%20happen.%26nbsp%3B%20If%2C%20instead%20of%20testing%20for%20a%20positive%20result%2C%20you%20test%20for%20'result1'%20being%20a%20number%2C%20an%20error%20in%20the%20first%20VLOOKUP%20will%20cause%20the%20second%20to%20be%20evaluated%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3D%20IF(%20ISNUMBER(result1)%2C%0A%20%20%20%20%20%20result1%2C%0A%20%20%20%20%20%20IF(LEN(result2)%3D0%2C%0A%20%20%20%20%20%20%20%20%20%20%20%22%22%2C%0A%20%20%20%20%20%20%20%20%20%20%20result2))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EI%20have%20removed%20the%20test%20on%20the%20length%20of%20'result1'%20because%20a%20number%20cannot%20have%20zero%20length.%26nbsp%3B%20I%20also%20removed%20surplus%20pairs%20of%20parentheses.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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 (New 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.