#N/A in VLookup - Can I use another function that will return 0?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
=if(iserror(vlookup(...)),0,vlookup(....))


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?
Click to expand...
 
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?
 
Yes, once to check if the VLOOKUP returns an error, once to get the result
if it doesn't.
 
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
 
=iferror() was added in xl2007, though.

Charles said:
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
Click to expand...
 
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 said:
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
Click to expand...
 
=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
 
Charles -- that works perfectly in my application; way more efficient.

Thanks,
Drew

Charles Moore said:
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
Click to expand...
 
Back
Top