Change #NA to 0

Hi All,

I have a formula below:

=VLOOKUP(D$2,CHOOSE(VLOOKUP($B3,$L$2:$M$8,2,0),TSBC2013!$C$5:$G$8,TSBC2013!$C$10:$G$12,TSBC2013!$C$14:$G$16,TSBC2013!$C$18:$G$20,TSBC2013!$C$22:$G$25,TSBC2013!$C$27:$G$28,TSBC2013!$C$30:$G$31),5,0)

When there's no entry it is coming out as #NA. I am trying to update #NA entries to 0.

I have tried =IFERROR and =IF(ISNA) but both coming out as "too many arguments error.

I have tried applying VBA:

"Cells.Replace "#N/A","",xlWhole" - but no change to my workbook when I apply.

"Cells.SpecialCells(xlCellTypeFormulas,xlErrors).Clear" - the #NA value is cleared but I need it to be 0 not empty field.


Can someone advise what am I doing wrong or is there any better solution than this?


Thanks in advance!

I used this:

=IFERROR(VLOOKUP(D$2,CHOOSE(VLOOKUP($B3,$L$2:$M$8,2,0),TSBC2013!$C$5:$G$8,TSBC2013!$C$10:$G$12,TSBC2013!$C$14:$G$16,TSBC2013!$C$18:$G$20,TSBC2013!$C$22:$G$25,TSBC2013!$C$27:$G$28,TSBC2013!$C$30:$G

and it worked.

Thanks!