In vlook if value comes to #n/a i want there 0

**satputenandkumar0** · 11-23-2012, 08:51 AM

Hi forum,

I am using below vlookup formula but if value comes to #N/A I want there "0" to be displayed.

=VLOOKUP(B8,raw1!B:I,8,0)/100000

Thank you

**Spencer101** · 11-23-2012, 08:53 AM

=IFERROR(VLOOKUP(B8,raw1!B:I,8,0)/100000),0)

**Fotis1991** · 11-23-2012, 08:53 AM

=iferror(VLOOKUP(B8,raw1!B:I,8,0)/100000),0)

**satputenandkumar0** · 11-23-2012, 09:04 AM

thanx to all for replying but it is not working

here i am attaching the file. pls do the needful changes.

Thanx

**vlady** · 11-23-2012, 09:10 AM

your file is xls

in your formula in sample file just change the "" in the middle to 0

**Special-K** · 11-23-2012, 09:11 AM

Try
=IFERROR(VLOOKUP(B8,raw1!B:I,8,0)/100000,0,VLOOKUP(B8,raw1!B:I,8,0)/100000)

Note this is not the same as previous suggestions

**Fotis1991** · 11-23-2012, 09:11 AM

=IF(ISNA(VLOOKUP(B8,raw1!B:I,8,0)/100000),0,(VLOOKUP(B8,raw1!B:I,8,0)/100000))

**Special-K** · 11-23-2012, 09:12 AM

Originally Posted by Fotis1991

=iferror(VLOOKUP(B8,raw1!B:I,8,0)/100000),0)

2 opening brackets, 3 closing ???

**Fotis1991** · 11-23-2012, 09:16 AM

I am a human. Not a machine. Hummans make mistakes. Even typo!

Don't you do mistakes?

=iferror(VLOOKUP(B8,raw1!B:I,8,0)/100000,0)

2 opening brackets, 2 closing!!!

**satputenandkumar0** · 11-24-2012, 02:07 AM

Dear Fotis1991,

Thanks lot it works fine.

Nandkumar

**sobujsf** · 11-24-2012, 05:36 AM

sundor hoati

Re: In vlook if value comes to #n/a i want there 0