How to avoid #N/A in results when using vlookup

[Abhinandan]

Dear All,

How can I avoid having #N/A as result when using vlookup.

A blank or Zero in result would be ok .

Regards,Abhi

 

[sudipto3003]

hi abhinandan,
use 'iferror' with vlookup to avoid #N/A
=iferror(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]),0))

 

[Colin Legg]

If you have Excel 2013+ and want to specifically target #N/A errors then you can use the IFNA() function.
=IFNA(VLOOKUP(Lookup_Value,Table_array,Col_index_num,Range_lookup),"")

If you have Excel 2007+ and want to target all errors you can use IFERROR().
=IFERROR(VLOOKUP(Lookup_Value,Table_array,Col_index_num,Range_lookup),"")

If you have earlier versions then you have to use a combination of IF() and either ISNA() or ISERROR().
=IF(ISERROR(VLOOKUP(Lookup_Value,Table_array,Col_index_num,Range_lookup)),"",VLOOKUP(Lookup_Value,Table_array,Col_index_num,Range_lookup))

 

[Somendra Misra]

The ISNA() option is also available in 2010.

Regards,

 

[Colin Legg]

The IFNA() option is also available in 2010.

Regards,

You must have a special version of Excel 2010!

I don't have Excel 2010/3 available to hand to double check this, but I think it was introduced only in 2013. It was one of the new 2013 worksheet functions I listed here.

 

[Somendra Misra]

@Colin Legg

See I already edited my post.

Regards,

 

[Colin Legg]

@Somendra Misra

Okay, cool.

 

[Somendra Misra]

Regards,

 

[Abhinandan]

Perfect Thanks...........