Removing #N/A from spreadsheet

**HMcCalla** · 06-28-2010, 05:27 AM

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

**martindwilson** · 06-28-2010, 05:30 AM

wrap formula in an if and isna
if(isna("your formula"),0,"your formula")

**royUK** · 06-28-2010, 05:30 AM

ISERROR or ISNA or use Conditional Formatting to hide

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

aiwnjoo · 06-28-2010, 05:31 AM

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

Or;

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

**HMcCalla** · 06-28-2010, 05:35 AM

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!!

**HMcCalla** · 06-28-2010, 06:14 AM

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

aiwnjoo · 06-28-2010, 06:18 AM

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

Not sure if you have to CTRL SHIFT ENTER

**martindwilson** · 06-28-2010, 06:49 AM

=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

**HMcCalla** · 06-29-2010, 05:26 AM

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

**royUK** · 06-29-2010, 05:37 AM

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
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save