# Microsoft Office Application Help - Excel Help forum > Excel General >  >  How to remove #n/a error in excel vlookup b/c value is not found?

## Clash

Morning all,

I have this formula; =VLOOKUP($A2,Name!$A:$C,2,FALSE) to join to spreadsheets, but in some cells I'm getting an #N/A error. Is there a way to remove or just return a blank cell?

Many thanks

Clash :Frown:

----------


## brokenbiscuits

=if(iserror(VLOOKUP($A2,Name!$A:$C,2,FALSE)),"",VLOOKUP($A2,Name!$A:$C,2,FALSE))

----------


## TMS

Or, in 2007:

=IFERROR(VLOOKUP($A2,Name!$A:$C,2,FALSE),"")


Regards

----------


## Clash

Many thanks both, 

also can you please explain why, if there is no reference number i.e. lookup ID number 1, and it's not in the lookup field it returns a "0" and is there any way to also leave it blank?

----------


## TMS

It's finding a blank somewhere in column A and returning the corresponding value in column B.  I'm assuming that's also blank otherwise a return of 0 is probably correct.

I'd need to look at the workbook to be sure.

Try:

=IFERROR(VLOOKUP($A2,Name!$A:$C,2,FALSE)&"","")

Regards

----------


## john91

You can use:
- ifna function for excel 2013
- iferror function for excel 2007, 2010, 2013
- if and isna functions for all excel vesion
Please watch excel tutorial video How to remove #N/A in vlookup and replace #n/a with 0 or a blank cell
https://www.youtube.com/watch?v=rInFQZsKLK0

----------

