Forum Discussion
#NA - ISERROR
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)
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.
2 Replies
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)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.
