SOLVED

#NA - ISERROR

Steel 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 (Steel 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)
1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel 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.

View solution in original post