# Removing #N/A from spreadsheet

1. ## Removing #N/A from spreadsheet

My spreadsheet is coming up with lots of #N/A and I know it's because the data my lookup formula is looking for isn't there. What do I need to add to the formula to remove these #n/a, and if nothing show as 0?

Many thanks

2. ## Re: Removing #N/A from spreadsheet

wrap formula in an if and isna

3. ## Re: Removing #N/A from spreadsheet

ISERROR or ISNA or use Conditional Formatting to hide

http://www.excel-it.com/excel_condit...formatting.htm

4. ## Re: Removing #N/A from spreadsheet

=IF(ISNA({cell-with-formula}),0,{cell-with-formula})

Or;

=IF(ISERROR(FORMULA),"0",FORMULA)

5. ## Re: Removing #N/A from spreadsheet

My formula is =VLOOKUP(A5,RANGE,4,FALSE) are you saying use the following

=if(ISNA(VLOOKUP(A5,RANGE,4,FALSE),0,VLOOKUP(A5,RANGE,4,FALSE))), because that didn't seem to work. Still doing something wrong!!

6. ## Re: Removing #N/A from spreadsheet

Any chance of someone writing out a sample formula that I can mirror please?

7. ## Re: Removing #N/A from spreadsheet

=if(iserror(vlookup(a5,range,4,false),0,vlookup(a5,range,4,false)

Not sure if you have to CTRL SHIFT ENTER

8. ## Re: Removing #N/A from spreadsheet

=if(ISNA(VLOOKUP(A5,RANGE,4,FALSE),0,VLOOKUP(A5,RANGE,4,FALSE)))
should be
=if(ISNA(VLOOKUP(A5,RANGE,4,FALSE)),0,VLOOKUP(A5,RANGE,4,FALSE))

and @ aiwnjoo this definitely does not need confirming with ctrl+shift+enter

9. ## Re: Removing #N/A from spreadsheet

Thanks to you all for your assistance yesterday. I've just updated and it now works. Martin thanks for your help in particular - Spot on. Cheers

10. ## Re: Removing #N/A from spreadsheet

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1