#N/A in VLookup - Can I use another function that will return 0? in Excel Worksheet Functions  
 |  Edit my Profile  |  Help
 
     
   
 
 
 
Arla 1/17/2006 11:09 AM PST
  Question
  I am using a VLookup function. I need to find an exact match, but when there
is no match, I would like to see a zero, rather than #N/A. Can someone help
me with a different formula to produce similar results as I am getting with
the VLookup without the #N/A?
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Dave Peterson 1/17/2006 11:16 AM PST
  Answer
  =if(iserror(vlookup(...)),0,vlookup(....))



Arla wrote:
>
> I am using a VLookup function. I need to find an exact match, but when there
> is no match, I would like to see a zero, rather than #N/A. Can someone help
> me with a different formula to produce similar results as I am getting with
> the VLookup without the #N/A?

--

Dave Peterson
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Arla 1/17/2006 11:33 AM PST
   
  I am sorry if I am being dense; so now that I have added the "iserror"
portion to the formula, do I need to have "vlookup" in two spots rather than
just once?

"Dave Peterson" wrote:

> =if(iserror(vlookup(...)),0,vlookup(....))
>
>
>
> Arla wrote:
> >
> > I am using a VLookup function. I need to find an exact match, but when there
> > is no match, I would like to see a zero, rather than #N/A. Can someone help
> > me with a different formula to produce similar results as I am getting with
> > the VLookup without the #N/A?
>
> --
>
> Dave Peterson
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Bob Phillips 1/17/2006 12:03 PM PST
  Answer
  Yes, once to check if the VLOOKUP returns an error, once to get the result
if it doesn't.

--

HTH

RP

"Arla" <Arla@discussions.microsoft.com> wrote in message
news:B72D1CC1-605A-4207-98E5-7F347621FEA4@microsoft.com...
> I am sorry if I am being dense; so now that I have added the "iserror"
> portion to the formula, do I need to have "vlookup" in two spots rather
than
> just once?
>
> "Dave Peterson" wrote:
>
> > =if(iserror(vlookup(...)),0,vlookup(....))
> >
> >
> >
> > Arla wrote:
> > >
> > > I am using a VLookup function. I need to find an exact match, but
when there
> > > is no match, I would like to see a zero, rather than #N/A. Can
someone help
> > > me with a different formula to produce similar results as I am getting
with
> > > the VLookup without the #N/A?
> >
> > --
> >
> > Dave Peterson
> >


 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Charles Moore 6/17/2008 4:35 PM PST
   
  Actually, not true. If you use the IFERROR function rather than
IF(ISERROR()), then you can do this:

=IFERROR(VLOOKUP(), 0)

This will return the result of the VLOOKUP if the VLOOKUP is successful but
will return the second parameter (in this case, 0), if the VLOOKUP is
unsuccessful.

This is definitely a more efficient approach than the IF(ISERROR())
construction since the VLOOKUP is performed only once.

-Charles


"Bob Phillips" wrote:

> Yes, once to check if the VLOOKUP returns an error, once to get the result
> if it doesn't.
>
> --
>
> HTH
>
> RP
>
> "Arla" <Arla@discussions.microsoft.com> wrote in message
> news:B72D1CC1-605A-4207-98E5-7F347621FEA4@microsoft.com...
> > I am sorry if I am being dense; so now that I have added the "iserror"
> > portion to the formula, do I need to have "vlookup" in two spots rather
> than
> > just once?
> >
> > "Dave Peterson" wrote:
> >
> > > =if(iserror(vlookup(...)),0,vlookup(....))
> > >
> > >
> > >
> > > Arla wrote:
> > > >
> > > > I am using a VLookup function. I need to find an exact match, but
> when there
> > > > is no match, I would like to see a zero, rather than #N/A. Can
> someone help
> > > > me with a different formula to produce similar results as I am getting
> with
> > > > the VLookup without the #N/A?
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
>
>
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Dave Peterson 6/17/2008 4:55 PM PST
   
  =iferror() was added in xl2007, though.

Charles Moore wrote:
>
> Actually, not true. If you use the IFERROR function rather than
> IF(ISERROR()), then you can do this:
>
> =IFERROR(VLOOKUP(), 0)
>
> This will return the result of the VLOOKUP if the VLOOKUP is successful but
> will return the second parameter (in this case, 0), if the VLOOKUP is
> unsuccessful.
>
> This is definitely a more efficient approach than the IF(ISERROR())
> construction since the VLOOKUP is performed only once.
>
> -Charles
>
> "Bob Phillips" wrote:
>
> > Yes, once to check if the VLOOKUP returns an error, once to get the result
> > if it doesn't.
> >
> > --
> >
> > HTH
> >
> > RP
> >
> > "Arla" <Arla@discussions.microsoft.com> wrote in message
> > news:B72D1CC1-605A-4207-98E5-7F347621FEA4@microsoft.com...
> > > I am sorry if I am being dense; so now that I have added the "iserror"
> > > portion to the formula, do I need to have "vlookup" in two spots rather
> > than
> > > just once?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > =if(iserror(vlookup(...)),0,vlookup(....))
> > > >
> > > >
> > > >
> > > > Arla wrote:
> > > > >
> > > > > I am using a VLookup function. I need to find an exact match, but
> > when there
> > > > > is no match, I would like to see a zero, rather than #N/A. Can
> > someone help
> > > > > me with a different formula to produce similar results as I am getting
> > with
> > > > > the VLookup without the #N/A?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> >
> >

--

Dave Peterson
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Rick F 6/22/2008 6:36 AM PST
   
  When VLookup finds a match, it returns the Lookup_Value itself. Is there a
way to return the position in the Table_array where VLookup found the match?

"Charles Moore" wrote:

> Actually, not true. If you use the IFERROR function rather than
> IF(ISERROR()), then you can do this:
>
> =IFERROR(VLOOKUP(), 0)
>
> This will return the result of the VLOOKUP if the VLOOKUP is successful but
> will return the second parameter (in this case, 0), if the VLOOKUP is
> unsuccessful.
>
> This is definitely a more efficient approach than the IF(ISERROR())
> construction since the VLOOKUP is performed only once.
>
> -Charles
>
>
> "Bob Phillips" wrote:
>
> > Yes, once to check if the VLOOKUP returns an error, once to get the result
> > if it doesn't.
> >
> > --
> >
> > HTH
> >
> > RP
> >
> > "Arla" <Arla@discussions.microsoft.com> wrote in message
> > news:B72D1CC1-605A-4207-98E5-7F347621FEA4@microsoft.com...
> > > I am sorry if I am being dense; so now that I have added the "iserror"
> > > portion to the formula, do I need to have "vlookup" in two spots rather
> > than
> > > just once?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > =if(iserror(vlookup(...)),0,vlookup(....))
> > > >
> > > >
> > > >
> > > > Arla wrote:
> > > > >
> > > > > I am using a VLookup function. I need to find an exact match, but
> > when there
> > > > > is no match, I would like to see a zero, rather than #N/A. Can
> > someone help
> > > > > me with a different formula to produce similar results as I am getting
> > with
> > > > > the VLookup without the #N/A?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> >
> >
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Pete_UK 6/22/2008 6:49 AM PST
   
  Use MATCH instead of VLOOKUP.

Hope this helps.

Pete

On Jun 22, 2:37 pm, Rick F <Rick F...@discussions.microsoft.com>
wrote:
> When VLookup finds a match, it returns the Lookup_Value itself. Is there a
> way to return the position in the Table_array where VLookup found the match?
>
>
>
> "Charles Moore" wrote:
> > Actually, not true. If you use the IFERROR function rather than
> > IF(ISERROR()), then you can do this:
>
> > =IFERROR(VLOOKUP(), 0)
>
> > This will return the result of the VLOOKUP if the VLOOKUP is successful but
> > will return the second parameter (in this case, 0), if the VLOOKUP is
> > unsuccessful.
>
> > This is definitely a more efficient approach than the IF(ISERROR())
> > construction since the VLOOKUP is performed only once.
>
> > -Charles
>
> > "Bob Phillips" wrote:
>
> > > Yes, once to check if the VLOOKUP returns an error, once to get the result
> > > if it doesn't.
>
> > > --
>
> > > HTH
>
> > > RP
>
> > > "Arla" <A...@discussions.microsoft.com> wrote in message
> > >news:B72D1CC1-605A-4207-98E5-7F347621FEA4@microsoft.com...
> > > > I am sorry if I am being dense; so now that I have added the "iserror"
> > > > portion to the formula, do I need to have "vlookup" in two spots rather
> > > than
> > > > just once?
>
> > > > "Dave Peterson" wrote:
>
> > > > > =if(iserror(vlookup(...)),0,vlookup(....))
>
> > > > > Arla wrote:
>
> > > > > > I am using a VLookup function.  I need to find an exact match, but
> > > when there
> > > > > > is no match, I would like to see a zero, rather than #N/A.  Can
> > > someone help
> > > > > > me with a different formula to produce similar results as I am getting
> > > with
> > > > > > the VLookup without the #N/A?
>
> > > > > --
>
> > > > > Dave Peterson- Hide quoted text -
>
> - Show quoted text -

 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Cornelius 7/4/2008 4:24 AM PST
   
  =if(iserror(vlookup(...)),0,vlookup(....))

is a really good solution to the problem. I am using this formular a lot. I
just found out that it is possible to create formulars with Microsoft Visual
Basic. I would like to create an easier version of the above mentioned
formular,
something like
=evlookup(..) for if iserror vlookup?

I dont have a lot of experience with MVB, does anybody know how to create
this?

Thanks Cornelius

 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
 
 
Gills 1/30/2009 11:10 AM PST
   
  Charles -- that works perfectly in my application; way more efficient.

Thanks,
Drew

"Charles Moore" wrote:

> Actually, not true. If you use the IFERROR function rather than
> IF(ISERROR()), then you can do this:
>
> =IFERROR(VLOOKUP(), 0)
>
> This will return the result of the VLOOKUP if the VLOOKUP is successful but
> will return the second parameter (in this case, 0), if the VLOOKUP is
> unsuccessful.
>
> This is definitely a more efficient approach than the IF(ISERROR())
> construction since the VLOOKUP is performed only once.
>
> -Charles
>
>
> "Bob Phillips" wrote:
>
> > Yes, once to check if the VLOOKUP returns an error, once to get the result
> > if it doesn't.
> >
> > --
> >
> > HTH
> >
> > RP
> >
> > "Arla" <Arla@discussions.microsoft.com> wrote in message
> > news:B72D1CC1-605A-4207-98E5-7F347621FEA4@microsoft.com...
> > > I am sorry if I am being dense; so now that I have added the "iserror"
> > > portion to the formula, do I need to have "vlookup" in two spots rather
> > than
> > > just once?
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > =if(iserror(vlookup(...)),0,vlookup(....))
> > > >
> > > >
> > > >
> > > > Arla wrote:
> > > > >
> > > > > I am using a VLookup function. I need to find an exact match, but
> > when there
> > > > > is no match, I would like to see a zero, rather than #N/A. Can
> > someone help
> > > > > me with a different formula to produce similar results as I am getting
> > with
> > > > > the VLookup without the #N/A?
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> >
> >
 
  Was this post helpful to you?  
 
 
  Reply | Print post   TopTop  
 
 
  Return to Microsoft Communities  Notify me of replies