VLOOPUP & #NA -- How to write a formula in VLOOKUP

[excelhelp1]

Hello Mr Excel
When I use a VLOOKUP i get some #NA. How can I write the VLOOPUP formula so if "NA" is the output it is defualted to "0"
Thanks
K

 

[Barrie Davidson]

Welcome to the board.

=IF(ISNA(your lookup formula),0,your lookup formula)

Regards,

 

[gaz_chops]

=if(isna(vlookup(.........)),0,vlookup(.........))
will return 0 if NA

 

[excelhelp1]

It works great !
Thanks GUYS...

 

[kaytee20]

Not sure if you guys are still on this board, but if so, I wanted to let you know that the solution below just saved my butt! Thanks!

 

[Dave350z]

Just used this one today, dead handy!!!!

 

[T. Valko]

Just used this one today, dead handy!!!!

If you're using Excel 2007 or later then you can use the new IFERROR function.

In older versions this is how you might do it:

=IF(ISNA(VLOOKUP(A1,C:D,2,0)),"",VLOOKUP(A1,C:D,2,0))

In Excel 2007 and later that can be reduced to:

=IFERROR(VLOOKUP(A1,C:D,2,0),"")

Note that the IFERROR function will trap ALL errors, not just the #N/A errors.

 

[Mark44stfc]

To add to the above point....

If your still on Excel 2003 the IF(ISERROR instead of IF(ISNA will also trap all errors

 

[Liliana163]

Hi,

IN G13 cell Iam getting N/A

=VLOOKUP($C13;'data 2013'!$B$5:$DK$150;$G$9;0)

Which other formula I can use and combined them to get 0 instead of N/A

Thank you