How to remove #n/a error in excel vlookup b/c value is not found?

**Clash** · 05-18-2011, 04:42 AM

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

**brokenbiscuits** · 05-18-2011, 04:47 AM

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

**TMS** · 05-18-2011, 04:50 AM

Or, in 2007:

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

Regards

**Clash** · 05-18-2011, 04:59 AM

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** · 05-18-2011, 05:58 AM

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** · 09-03-2015, 11:34 PM

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