May 04 2021 06:37 PM
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)
May 04 2021 07:38 PM
Solution
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.
May 05 2021 04:55 AM
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)
May 04 2021 07:38 PM
Solution
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.