Feb 24 2020 01:07 AM
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
Feb 24 2020 01:54 AM
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))))),"")
Feb 24 2020 02:44 AM
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.
Feb 24 2020 01:54 AM
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))))),"")