SOLVED

#NA - ISERROR

%3CLINGO-SUB%20id%3D%22lingo-sub-2324416%22%20slang%3D%22en-US%22%3E%23NA%20-%20ISERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2324416%22%20slang%3D%22en-US%22%3E%3CP%3EExperts%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20display%20a%200%20on%20%23NA%3F%26nbsp%3B%20I%20thought%20the%20last%200%20would%20do%20so%20but%20it%20still%20says%20%23NA.%26nbsp%3B%20I%20also%20tried%20to%20wrap%20with%20ISERROR%20but%20maybe%20I%20did%20it%20wrong.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethank%20you.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(%5B%40%5BIssued%20Or%20Pending%5D%5D%20%3D%20%22Issued%22%2CINDEX(tblOutstanding%2C%3CBR%20%2F%3EMATCH(A5%2CtblOutstanding%5BBeneficiary%5D%2C0)%2C%3CBR%20%2F%3EMATCH(TEXT(EOMONTH(TODAY()%2C-1)%2B1%2C%22m%2Fd%2Fyyyy%22)%2CtblOutstanding%5B%23Headers%5D%2C0))%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2324416%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2324468%22%20slang%3D%22en-US%22%3ERe%3A%20%23NA%20-%20ISERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2324468%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can't%20take%20the%20time%20to%20decipher%20your%20formula.%20Let%20me%20just%20offer%20this%3A%20when%20I'm%20writing%20a%20conditional%20formula%2C%20I%20first%20work%20to%20get%20it%20to%20work%20correctly--which%20includes%20giving%20me%20an%20%23NA%20or%20other%20error%20when%20such%20errors%20are%20%22the%20right%20answer%22...once%20it's%20working%20correctly%20and%20I%20now%20want%20to%20wrap%20with%20IFERROR%20it's%20easy%2C%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(%3C%2FSTRONG%3E%3CEM%3Eworking%20formula%3C%2FEM%3E%3CSTRONG%3E%2C%22%22)%3C%2FSTRONG%3E%3CSPAN%3E%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eor%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIFERROR(%3C%2FSTRONG%3E%3CEM%3Eworking%20formula%3C%2FEM%3E%3CSTRONG%3E%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20potentially%20counter-intuitive%20thing%20about%20%3CSTRONG%3EIFERROR%3C%2FSTRONG%3E%20is%20that%20you%20put%20the%20working%20(non-error)%20formula%20first%2C%20and%20your%20replacement%20if%20there%20IS%20an%20error%20comes%20last.%20It's%20backward%20compared%20to%20the%20standard%20%3CSTRONG%3EIF%3C%2FSTRONG%3E%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2326017%22%20slang%3D%22en-US%22%3ERe%3A%20%23NA%20-%20ISERROR%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2326017%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1029845%22%20target%3D%22_blank%22%3E%40Tony2021%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFNA(IF(%5B%40%5BIssued%20Or%20Pending%5D%5D%20%3D%20%22Issued%22%2C%0A%20%20%20%20INDEX(tblOutstanding%2C%0A%20%20%20%20%20%20%20%20MATCH(A5%2CtblOutstanding%5BBeneficiary%5D%2C0)%2C%0A%20%20%20%20%20%20%20%20MATCH(TEXT(EOMONTH(TODAY()%2C-1)%2B1%2C%22m%2Fd%2Fyyyy%22)%2CtblOutstanding%5B%23Headers%5D%2C0)%0A%20%20%20%20)%2C%0A0)%2C%200)%0A%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Frequent Contributor

Experts,

 

How can I display a 0 on #NA?  I thought the last 0 would do so but it still says #NA.  I also tried to wrap with ISERROR but maybe I did it wrong.  

 

thank you. 

 

=IF([@[Issued Or Pending]] = "Issued",INDEX(tblOutstanding,
MATCH(A5,tblOutstanding[Beneficiary],0),
MATCH(TEXT(EOMONTH(TODAY(),-1)+1,"m/d/yyyy"),tblOutstanding[#Headers],0)),0)

2 Replies
best response confirmed by Tony2021 (Frequent Contributor)
Solution

@Tony2021 

 

I can't take the time to decipher your formula. Let me just offer this: when I'm writing a conditional formula, I first work to get it to work correctly--which includes giving me an #NA or other error when such errors are "the right answer"...once it's working correctly and I now want to wrap with IFERROR it's easy,

=IFERROR(working formula,"")

or

=IFERROR(working formula,0)

 

The potentially counter-intuitive thing about IFERROR is that you put the working (non-error) formula first, and your replacement if there IS an error comes last. It's backward compared to the standard IF function.

@Tony2021 

That could be like

=IFNA(IF([@[Issued Or Pending]] = "Issued",
    INDEX(tblOutstanding,
        MATCH(A5,tblOutstanding[Beneficiary],0),
        MATCH(TEXT(EOMONTH(TODAY(),-1)+1,"m/d/yyyy"),tblOutstanding[#Headers],0)
    ),
0), 0)