Thanks:  0
Likes:  0

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

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

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

2. ## Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

Welcome to the board.

Regards,

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

4. ## Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

It works great !
Thanks GUYS...

5. ## Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

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!

6. ## Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

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

7. ## Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

Originally Posted by Dave350z
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.

8. ## Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

To add to the above point....

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

9. ## Re: VLOOPUP & #NA -- How to write a formula in VLOOKUP

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•